General

The following are some general tips for writing efficient SQL code:

• Using SET NOCOUNT ON and SET NOCOUNT OFF inside the stored procedure after the CREATE statement and before the last RETURN reduces the number of reads considerably on large stored procedures.
• All the objects that are referenced within the same stored procedure should all be owned by the same object owner (preferably dbo).
• Avoid using NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, and instead try to use one of the following options: Use EXISTS or NOT EXISTS, Use IN, Perform a LEFT OUTER JOIN and check for a NULL condition OR my preferred option use a calculated field so you can use an "=" in the WHERE clause.
• Avoid using the SUBSTRING function and use the LIKE condition instead.
• When there is a choice of using the IN or the BETWEEN clauses in your Transact-SQL, the BETWEEN is generally more efficient.
• When there is a choice of using the IN or the EXISTS clause in your Transact-SQL, the EXIST clause is generally more efficient.
• The GROUP BY clause can be used with or without an aggregate function but for situations without an aggregate function the SELECT DISTINCT option should be used instead.
• The table hint NOLOCK (for example ‘WITH (NOLOCK)’) should be used as much as possible.