DBA recommendations for writing Stored Procedure

1. Try to use derived tables when possible, instead of temporary tables to gain better performance.
ex:
SELECT MIN(Salary)
FROM (SELECT TOP 5 Salary FROM Employees ORDER BY Salary Desc)

2. Do not use 'SELECT * from table'. Only return the columns you want/need. This brings more data back over the network and also eliminates the use of some indexes (covering indexes). This may also screw up apps that are expecting a certain number of columns when a column is added.

3. Use the @@error global variable for error-handling, but store the value into your own user variable, as the value will be reset after the next statement. All Global variables should be stored into a local variable if they are going to be used later in processing.

4. Always 'SET NOCOUNT ON' at the beginning of stored procedures. This will reduce the network round trips your SQL will have to make.

5. Avoid using 'print' in SPs unless it is needed. Print statements cause network trips and acknowledgments from the client. If the client does not need to know - or nothing is receiving the 'print' - this is unneeded.

6. Develop and stick with a standard naming convention for your area/project.

7. Never use a wildcard character (%) at the beginning of a search string as it will always result in a table scan.

8. When doing an insert in an SP - Always specify the column names - to avoid issues when a column is added to the table.

9. ALWAYS specify the 2 part name when executing SPs.
ex: EXEC dbo.sp_proc. When the 2 part name is not used SQL Server first checks for an SP owned by the executer, then dbo.

10. Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks.

11. Don't use the prefix "sp_" in a stored procedure name. The reason for this is that whenever a stored procedure is executed with the prefix of 'sp_' SQL Server first looks in the MASTER database to execute it - if it does not find it in MASTER it will then use the current database.

12. If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure.

13. Avoid nesting transactions in stored procedures. If you need transaction support from SP to SP use a savepoint.

14. If your SP does not require 'transaction safety' use the 'NOLOCK' table hint. A table hint lets the SQL engine ignore and not perform locks for a given operation.

15. Try to avoid using temporary tables inside your stored procedures. Use Table Variables if possible. Temp Tables Reduce the chance of plan reuse. Table variables also have less locking overhead, making them faster.

16. Place all DDL language at the top of the Stored Procedure. All DML should follow. This will increase the chance of plan reuse.

17. Use the 'TOP' operator over the 'SET ROWCOUNT' command to limit the number of rows returned, as there is less overhead. (And ROWCOUNT is going away after SQL2K).

18. Use Global temp tables only if you absolutely have to.

19. Clearly document and mark any optimizer hints.

20. Limit your joins to 4 tables or less. The SQL Server optimizer is only so smart (and so is your DBA).

21.Do not use a function in the where clause.
For example if you are looking for records with a timestamp greater
than 7 days old don't say
where ts > getdate() - 7.
Set a variable "SELECT @date = getdate() -7" and then use:
Where ts > @date

22. Do not use ‘Where not exists’.
Instead use a left outer join where the left side is null