Stored procedures

• EXCEPT or NOT EXIST clause can be used in place of LEFT JOIN or NOT IN for better performance (see example for EXCEPT below)

SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 1000
EXCEPT
SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 2000
ORDER BY EmpName;

• If stored procedure always returns single row resultset, then consider returning the resultset using OUTPUT parameters instead of SELECT statement
• Use query hints to prevent locking if possible (NoLock)
• Avoid using dynamic SQL statements if you can write T-SQL code without using them.
• The number of nested procedures must be limited to no more than 32