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.
Use schema name
... on used tables. Put procedure in custom schema without data.
Use tables
... not views, other procedures and triggers. And only functions with careful big-O considerations
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.
Use sp_executesql
... for dynamic SQL. Not EXECUTE. In general you should avoid dynamic SQL.
Use specific columns
... only take data that are required.
Use order of columns
... as given in the tables. One table a time.
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.
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.
Check values
... before usage. Especially parameter values!
Use effective types
... and avoid custom types.
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.
Use EXISTS
... not COUNT.
Use effective error handling
... by testing before exception handling with TRY - CATCH. Consider exception handling as last-line-of-defence after tests and checks.
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.
Set security explicit
... on procedure through custom database roles on Active Directory groups.
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
Posts
