Best Practices : Writing Stored Procedures
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
Avoid not joining many tables? That doesn’t sound right…
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
Hi there. All good and well… but you should almost always refrain from using hints in your production code, especially the dreaded NOLOCK. NOLOCK is VERY(!) dangerous and is equivalent to the READUNCOMMITED isolation level. And hints in and by themselves are a mechanism to bypass the (sophisticated and dynamic!) rules of the query optimizer. What happens when the rules change with the next release? With hints you not only get a maintenance nightmare but also risk very sub-optimal code. Also, the rule about seeks is not correct. Seeks are good for OLTP scenarios when your query is trying to target very few rows in a table. Scans is what you want in data warehousing. So, in a word – it all depends. Error handling and transaction management is best left to SET XACT_ABORT ON… There are many more reservations I’d have but don’t have time to expand upon them.