Here are some personal guidelines on writing and using a stored procedure. They are mentioned in a somewhat random order. The list and each recommendation will most likely change over time with new experinces, feedback and other new knowledge.
… to avoid unnecessary activity and communication on row count.
… to roll back most transactions on error. See details from Sommarskog (link) and Swart (link).
… to make the code a little more robust and strict. Also some protection against injection. Still I would prefer to enclose object and other identifiers in square brackets. This I think makes the code more strict and easy to maintain with several developers and teams involved.
Use schema name
... on used tables. Put procedure in custom schema without data. Do not use the schemas dbo or sys as they are not in your control.
Use tables
... not functional objects such as views, other procedures and triggers. And only use functions in stored procedure with careful big-O considerations. This could only be worse with the use of nested views, procedures, functions, triggers and other functional objects.
Use sets
... not cursors or sub-selects as SQL Server is set-based. Cursors are fine when on DB2 or Oracle. Sub-selects are often seen on SAS.
Use functional name
... not sp_ or other Hungarian naming. It doesn´t really matter what type the object is.
hellip; for dynamic SQL. Not EXECUTE. In general you should avoid dynamic SQL. This includes prepared statements.
Use specific columns
... only take data that are required. Network traffic is then reduced to the required amount.
Use order of columns
... as given in the tables. One table a time. Re-arranging the columns could add significant resource consumption on each call.
Use keys
... and indexes with care. Use index on foreign keys. Check for covering index. Index usage might change with change of data in used tables.
Use table variables
... not temporary tables. This will keeps activity in buffer and away from storage,
Be direct
... and avoid temporary cached or staged data. Do not use dirty reads!
Be short
... by short transactions. Avoid costly isolation levels such as Repeatble Read. Also you can save some network traffic on often used stored procedures by short names on the procedure and the parameters.
Use effective parameters
... and avoid parameter sniffing. Focus on parameters where the values change a lot. Convert these parameters to local variables when suitable.
Check values
... before usage. Especially parameter values! Do the value check inside the procedure before usage.
Use effective types
... and avoid custom types. Custom types will add resource consumption on resources. And in some situation increase blockings on metadata.
Use effective operators
... avoid costly operators such as LIKE. Be carefull with COALESCE, string operations and aggregated values. T-SQL is for finding and sorting data. Use other languages for complex calculations and text processing.
Use explicit type cast
... not indirect or implicit. And you are still somewhat in control.
Use EXISTS
... not COUNT. As this is more effective with T-SQL.
Use effective error handling
... by testing before exception handling with TRY - CATCH. Consider exception handling as last-line-of-defence after tests and checks. Even when using exception handling this should be kept inseide the procedure. Throwing a exception outside the procedure could be rather costly on the resource consumption.
Use MS_Description
... extended property. This helps development with Visual Studio, see SQLAdmin on Descriptions.
Document
... procerdure, each parameter and output/return value in code, detailed and with examples. Also when you are the one and only developer and user.
Set security explicit
... on procedure through custom database roles on Active Directory groups. Using default database roles is outside your control.
Use ALTER PROCEDURE
... not DROP - CREATE PROCEDURE. Security and other external configurations will be lost on each change.
Monitor
... usage and performance with DMVs, XEvents etc. See Monitoring Performance of Natively Compiled Stored Procedures.
Call procedure effective
... with CommandType.StoredProcedure and SqlParameter objects on the SqlCommandobject. Use careful crafted connection string with secure authentication (SSPI) like Kerberos.
Reference
Kimberly Tripp, SQLSkills: Building High Performance Stored Procedures.
Top 10 stored procedure performance tuning tips in SQL server
History
2026-01-02 : Section on XACT_ABORT added.
2024-07-03 : Post published.
Posts
