1)Do use the Transactions in long running queries and many small-small queries
2)DON’T Use SELECT *
3)Avoid explicit or implicit functions in WHERE clauses
4) Avoid long actions in triggers if using
5) Use temporary tables and table variables appropriately
6)As a good rule of thumb try to replace all sub queries with joins
7)Order of Tables in Join Conditions.
10)Can use Fixed Index Hint while using the conditions
11)Size the data type wisely in the SP during declaration.
12)Efficient use of MAXDOP setting within query.
13)GROUPING SETS in SQL 2008
14)Always use the With rowlock, with no lock where occurring deadlocks.
15)Use Update From Rather than simply Update
16) UNION ALL instead of UNION
17) Avoid expensive operators such as NOT LIKE