This article includes set of 40 recommendations for stored procedures.

Following checklist can be used by Developers during development of stored procedure. Leads can refer this checklist for review of the stored procedure submitted for code review.

This checklist can also come handy to Database Developers and Administrators while working on Performance Tuning/Optimization of stored procedures.

1. SET NOCOUNT ON should be there at the beginning of Stored Procedure.
2. SET TRANSATION ISOLATION LEVEL READ UNCOMMITED should be there at the beginning of Stored Procedure, unless you intentionally want to avoid dirty read. Please keep in mind, reading committed record can cause read locks which will result into frequent blockages.
3. All temporary tables should be created in the beginning. All the DDL (DROP, CREATE etc.) statements should be on the top of the stored procedure. Each temporary table should have Primary Key & Clustered Index. Possibly Primary Key & Clustered Index can be on the same column. Also if the temporary table contains more rows then the Index should be provided on the logical columns.
4. Variable declaration & values assignment to the variable should be next to the temporary table creation.
5. Code should be broken down into logical groups and encapsulated in BEGIN – END block with proper comment at the top of BEGIN.
6. Parameter sniffing must be taken into consideration while providing the default values to the input parameters of the stored procedure.
7. Object, Column and Variable name should follow the naming convention of your organization.
8. Always mention the SCHEMA name along with every object name (Table, Function, View, Stored Procedure). For e.g. [dbo].[Customer]. This will help avoid the object look up cost. This may matter a lot, if there are too many objects used in the stored procedure.
9. Table Variables should be avoided for large data sets. Please note, table variables do not have statistics. Therefore, the optimizer uses the hard-coded estimation of 1 row returned from a table variable irrespective of whatever number of rows you have in a table variable. This can lead to poor execution plan on the large data sets. Table variables supports Primary and Unique Key with Clustered or Non Clustered Index but you cannot explicitly add indexes on table variable. Table variable is important feature of SQL and should be used appropriately. It can be very helpful with relatively small data sets.
10. Functions should not be used in joins. Output of function should be first taken into some temporary table and then use the temporary table in the join.
11. View to View join should be avoided.
12. Linked Server should not be used in JOIN. Output of the view should be first taken into some temporary table with only specific set of required columns and then use temporary table in the JOIN.
13. ORDER BY Clause should be avoided in SELECT statement, If required, columns sequence no should be given in order by clause rather than column name. More preferred candidate for the ORDER BY is Clustered Index column and that too in the same sort order as defined in the Clustered Index. Clustered Index in SQL Server
14. Avoid writing big code for which some inbuilt feature is available in the SQL Server. Start utilizing the same. You can explore ranking functions, analytical functions and other windowing functions. These functions can help you shorten the code and as you know less code means less maintenance and better performance.
15. WHERE clause should be there in every query. If in any case, WHERE clause is not there then the filtering cost must be taken care while writing the query.
16. While writing the WHERE, JOIN, SELECT Clause, the columns to be placed in the same sequence as the columns defined in the Index. Before defining the WHERE, JOIN, SELECT clause, first check if there are any available index, if yes then follow the sequence.
17. Code should be properly indented with sufficient, proper & easily understandable comments.
18. All the heavy operations like UPDATE, DELETE statements should contain the WHERE clause and the WHERE clause must contain the column having CLUSTERED INDEX as the first condition.
19. On high workload database, always check the possibility to avoid UPDATE after due feasibility study. UPDATE is the most costliest operation in SQL Server.
20. If data volume is large, execution should be done in batches. Batching in SQL Server
21. Unused joins are an overhead and Performance killer. Avoid such joins. Does it makes sense placing a join on a table, but not using any column from that table? Generally, join is applied either to fetch or filter records.
22. Joins should be properly used. If matching records from both the tables are required then INNER JOIN must be used. LEFT JOIN should not be treated as default solution everywhere.
23. SELECT statement must contain only those columns that are required. Unnecessary column must not be there in the SELECT statement.
24. If any table contains more rows and that table is required in many joins then extract the required/filtered row from that table into some temporary table and use the temporary table in the join.
25. Don’t use CURSOR. Use WHILE loop in place of cursor. Although CURSOR has some definite use and is very useful in certain scenarios.
26. Don’t use SUBQUERY in JOIN. Use CROSS APPLY, OUTER APPLY in place of SUBQUERY.
27. Try to avoid using scalar functions in SELECT statements which internally refers to physical tables. Instead convert the logic to JOIN. Trust me, I’ve observed performance gains up to 1000℅ with this simple change. Workaround to scalar function in SQL Server
28. To check existence of record, use EXISTS, NOT EXISTS clauses. EXISTS () & NOT EXISTS () functions in SQL Server
29. Before writing new Stored Procedure/Function, check if you have any existing available logic for the same purpose to achieve the re-usability.
30. Avoid INSERT INTO <table name> SELECT *. Column names should be specified both in INSERT as well as in SELECT. Otherwise, the moment any existing column will be renamed/removed or new column will be introduced, the query will fail with error.
31. Do not use reserved words for column names.
32. All object names such as table name, column name, stored procedure name, function name, view name etc. should be within square bracket [].
33. With Begin and End Transaction always use global variable @@ERROR immediately after data manipulation statements (insert/update/delete) so that if there is an error the transaction can be rollback.
34. SQL Server comes with beautiful error handling mechanism such as BEGIN TRY .. END TRY, BEGIN CATCH .. END CATCH, THROW etc. Make proper use of it.
35. Normalization is the beauty and can say core of all the RDBMS including SQL Server. But when we talk about high workload database then every single decision matters. Do not always try to achieve Normalization at the cost of performance. Normalization vs Performance in SQL Server
36. If multiple Transactions are used in stored procedure then declare SET XACT_ABORT ON on the top of stored procedure preferably below SET NOCOUNT ON, unless you intentionally do not want to do it.
37. Use the data type as same to what is defined in the table.
38. Avoid Dynamic SQL. Although Dynamic SQL is an important feature and should be used appropriately. Implementing Code and Data Decoupling using Dynamic SQL to achieve high performance and concurrency
39. Implement Error Handling in every stored procedure and properly return the Error Message, Object Name, Error Code and Line Number.
40. Implement debugging in complex stored procedures and return the debugging trace using output parameter and/or error handling.

• Database Design & Architecture
• Performance Tuning
• SQL Server