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 and TRANSATION ISOLATION LEVEL READ UNCOMMITED should be there at the beginning of Stored Procedure.
  2. 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.
  3. Variable declaration & values assignment to the variable should be next to the temporary table creation.
  4. Code should be broken down into logical groups and encapsulated in BEGIN – END block with proper comment at the top of BEGIN.
  5. Parameter sniffing must be taken into consideration while providing the default values to the input parameters of the stored procedure.
  6. Object, Column and Variable name should be in Title case.
  7. Alias given to tables should be logical and in upper case, Alias name should not be more than 5 characters.
  8. Table Variables should be avoided if it’s going to hold more than one row.
  9. 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.
  10. 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.
  11. Avoid writing big code for which some inbuilt feature is available in the SQL Server 2008 R2. Start utilizing the same.
  12. 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.
  13. 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.
  14. Code should be properly indented with sufficient, proper & easily understandable comments.
  15. 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.
  16. If data volume is large execution should be done in batches.
  17. Tables whose columns are not used in query should not be there in joins.
  18. Joins should be properly used. If output from both the tables is required then INNER JOIN must be used like wise.
  19. SELECT statement must contain only those columns that are required. Unnecessary column must not be there in the SELECT statement.
  20. 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.
  21. Don’t use CURSOR. Use WHILE loop in place of cursor.
  22. Don’t use SUBQUERY in JOIN. Use CROSS APPLY, OUTER APPLY in place of SUBQUERY.
  23. To check existence of record, use EXISTS, NOT EXISTS clauses.
  24. Before writing new Stored Procedure/Function, discuss within the team and check whether we have any existing available logic for the same purpose to achieve the re-usability.
  25. Avoiding SELECT * and INSERT without column names.
  26. Do not use reserved words for column names.
  27. 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.
  28. Use the data type as same to what is defined in the table.
  29. Avoid Dynamic SQL.
  30. Implement Error Handling in every stored procedure and properly return the Error Message, Object Name, Error Code and Line Number.
  31. Implement debugging in complex stored procedures and return the debugging trace using output parameter and/or error handling.

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: Logo

You are commenting using your 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