1.Never use embedded T-SQL; always use stored procedures.

2.       Never do a select * even if you need all fields from a table or view. This makes code more efficient, reusable and is much less likely that a change may affect an application as it may change the order of the columns etc. The column set should be kept as short as possible.

 

3.       Always obtain execution plan to find table scans or inefficient queries.

 

4.       Don’t return rows that you don’t need. Use the WHERE clause, SELECT TOP or use SET ROWCOUNT X. In SQL 7.0 always use SELECT TOP instead or ROWCOUNT. In SQL 6.5 when setting ROWCOUNT always set it to 0 again after the query.

 

5.       Before executing queries check whether the required values have been passed.

 

6.        Avoid using wildcard (LIKE %) text searches, specially with % at the beginning.

 

7.        Don’t use “ = NULL”, use “IS NULL”.

 

8.       Always add a header with details like a description, author, date, modifications, etc.

 

9.       Use in-line comments when possible. This will make it easier for someone else to understand the queries.

 

10.    Avoid all use of cursors.

 

11.    If possible avoid negative predicates (use of NOT) which always cause a table or index scan.

 

12.    Use the IN keyword in your WHERE clause to specify several conditionals instead of using several OR statements. For example, use this:

WHERE cCustomerState IN (‘CT’, ‘NY’, ‘NJ’)

Instead of this:

WHERE cCustomerState = ‘CT’ OR cCustomerState = ‘NY’ OR cCustomerState = ‘NJ’

 

13.    If you have a trigger on a table that is likely to be bulk changed, do include all code in the trigger rather than calling a stored procedure from it. This will perform better than recurrently calling the SP for each changed record.

 

 

 

 

Further reading:

 

-          Transact-SQL SQL Server Performance Tuning Tips.

http://www.sql-server-performance.com/transact_sql.asp.