Best practices for Stored Procedures in SQL Server

Read the following blog post for Best Practices to be followed for designing the tables in SQL Server.

Best practices for designing tables in SQL Server

  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. Alias given to tables should be logical and in upper case, Alias name should not be more than 5 characters.
  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 mos costliest operation in SQL Server.
  20. If data volume is large, execution should be done in batches. Batching in SQL Server
  21. Tables whose columns are not used in query should not be there in joins.
  22. Joins should be properly used. If output from both the tables is required then INNER JOIN must be used like wise.
  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 table. Instead convert the logic to JOIN. Trust me, have achieved up to 1000℅ performance gain after doing it. 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. Avoiding SELECT * and INSERT without column names.
  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 al 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. Decoupling and Dynamic Data Archival in SQL Server 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.
Advertisements

One thought on “Best practices for Stored Procedures in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s