RSS

Best Practices : Writing Stored Procedures

14 Mar

Hi All,

I would like to share a handy list which I found today from my old collection during files clean-up. These are few quick tips that every developer should bear in mind while writing T-SQL codes particularly Stored Procedures.

It’s always hard to find the best version of such list but we can definitely make a better version, so you are welcome to make this as a better-list by adding/editing below tips.

•    Avoid SELECT INTO whenever possible
•    Avoid Cursors whenever possible
•    Avoid using SELECT *
•    Avoid using SQL Server’s reserved keywords
•    Avoid using GOTO statements
•    Avoid using Large Temp Tables
•    Avoid function based where clauses & joins
•    Avoid dynamic SQL statements whenever possible
•    Use WITH (NOLOCK) in select statements (Preferably for ETL operations)
•    Use fully qualified object names e.g. [Schema].[Table]
•    Use “History Header Box” for describing purpose, modifier, usage etc.
•    Use indexes for Temp tables if they are in join
•    Use LIKE, IN and BETWEEN Operators wherever possible
•    Use sp_executesql instead of EXEC
•    Use Scope_identity() instead of @@identity
•    Use more readable ANSI-Standard Join clauses
•    Use column list in your INSERT statements
•    Use CTE, Table Variable or Temp tables based requirement, wisely
•    Use Transactions(Begin-Commit-Rollback), Try-Catch blocks whenever necessary
•    Use WITH ENCRYPTION option wisely (only when needed)
•    Put in-line comments for quick explanation of code
•    Use UNION and UNION ALL  wisely
•    Use Begin-End to bind complete stored proc’s code
•    Don’t Use GRANT in sql scripts (It should be handled separately)
•    Do not nest views inside other views
•    Put in-line comments for quick explanation of code
•    Keep Transactions as short as possible to avoid blocking & deadlocks
•    Update with rowlock hint
•    Transactions should be finite
•    Error Handling should be implemented
•    Declare variables at start of SP
•    Drop temp tables used in sql script or SP at end
•    Ensure query use index seek not table scans
•    Avoid not joining many tables
•    Have a habit of formatting code from starting for easy reading/review (indentation)
•    Carefully use SET NOCOUNT ON/OFF
•    Assign the parameter values to local variables in stored proc (Parameter Sniffing)
•    When more tables are joined avoid using Group BY
•    Write a query with limited scope

I hope this will be helpful to you somewhere.
Thanks, Khilit

 
2 Comments

Posted by on March 14, 2012 in SQL Server, Suggestion, T-SQL

 

Tags: , , ,

2 Responses to Best Practices : Writing Stored Procedures

  1. Aaron

    June 20, 2012 at 1:28 am

    Avoid not joining many tables? That doesn’t sound right…

     
    • Khilitchandra Prajapati

      June 20, 2012 at 8:16 pm

      Hi,
      yes you are right, its not a general case.

      one of the reason we write stored proc is a functional modularity. but if you have lots of tables joined in SP and we are calling that SP many times then we should definately look for some good solution. so, when this is the case, we should need to review the number of joins we do in SP.

      here is the note i could find quickly from google: http://www.devx.com/dbzone/Article/33551/1954
      Section “Tips for Writing High-performance SQL” -> Point No. 7

      if you have more things add to this list pls share with us.
      Thanks, Khilit

       

Leave a Reply

 
%d bloggers like this: