There are some of the guidelines for the developers to assist do’s and don’ts during the coding practices.
This is by no mean a complete coverage of the Guidelines; there are other standard as well which can’t be covered in this Document.

1)Do use the Transactions in long running queries and many small-small queries

If there is expected a long running transactions and expecting large records output, developer should always use the transactions within the BEGIN TRAN and END TRAN.
This way transaction holds itself a place in buffer cache for individual transactions and memory is specifically assigned to it making a faster processing.

2)DON’T Use SELECT *

This is because if you selecting all the records from the table, it will buffer unnecessary columns as well pushing the round trip of the IO to the disk and memory.

3)Avoid explicit or implicit functions in WHERE clauses

Like Convert ()

4) Avoid long actions in triggers if using

5) Use temporary tables and table variables appropriately

Always try to use the Table variable when result set is small and Temp Table when result set is considerably large.

6)As a good rule of thumb try to replace all sub queries with joins

Sub queries treat itself as inline code whereas Joins use itself as Table which is faster.
If possible try to avoid the sub queries within join

7)Order of Tables in Join Conditions.

If possible always use the smaller table in Join condition first and bigger tables in the end moving forward.

8)Loop optimization

Should try to put all operations outside of the loop if they don’t change inside
This reduces the amount of unnecessary repetitive work. SQL Server optimizer doesn’t automatically recognize such inefficiencies and clean the code for you (compilers of some other languages do)

9)Parameter sniffing

Developers should not use the SP parameters within the SP on the fly which causes the “Parameter Sniffing”. Use the Parameters of the SP after declaring and setting. Due to this issue SP behaves differently every time during run.

10)Can use Fixed Index Hint while using the conditions

Like in 2008, we can use with Index hint and also fixed plan hint to force the hint to queries to make it run faster
a) With (index=1)
Like
b) OPTION ( OPTIMIZE FOR

11)Size the data type wisely in the SP during declaration.

Developer randomly declares the data type size which is of no use like: Varchar (500). It increases unnecessary reserved space in buffer during run.

12)Efficient use of MAXDOP setting within query.

Need to ask the DBA’s for the setting of Quad core/CPU availability including the memory setting then use the hint appropriately which could improve the query drastically

13)GROUPING SETS in SQL 2008

There is new feature in SQL 2008 to use the Grouping Set instead of Group By in all the Unions.
This way optimizer will not make a plan every time group by occurs in all Union.

14)Always use the With rowlock, with no lock where occurring deadlocks.

TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is one substitute

15)Use Update From Rather than simply Update
UPDATE titleauthor
SET royaltyper = 90
WHERE au_id = (SELECT au_id FROM authors
WHERE au_lname = ‘Ringer’ AND au_fname = ‘Albert’)
AND title_id = (SELECT title_id FROM titles
WHERE title = ‘Life without Fear’)
Update from
UPDATE titleauthor
SET royaltyper = 90
FROM authors a, titles t
WHERE titleauthor.au_id = a.au_id
AND a.au_lname = ‘Ringer’
AND a.au_fname = ‘Albert’
AND titleauthor.title_id = t.title_id
AND t.title = ‘Life Without Fear’

16) UNION ALL instead of UNION
UNION has a “side-effect” of eliminating all duplicate rows and sorting results, which UNION ALL doesn’t do
Selecting a distinct result requires building a temporary worktable, storing all rows in it and sorting before producing the output

17) Avoid expensive operators such as NOT LIKE