Best Practices – Stored Procedures

The following suggestions may improve the performance of a Stored Procedure:

• Use SET NOCOUNT ON statement at the top of a stored procedure to turn off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. It can greatly improve the overall performance of the database and application by removing extra overhead from the network.
  
• Use schema names when creating or referencing database objects in the procedure. It will take less processing time for the Database Engine to resolve object names if it does not have to search multiple schemas. It will also prevent permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema. To check schema names for all the tables in a database, use the following statement:
  SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + name + ']'
  AS SchemaTable
  FROM sys.tables
  
• Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.
  
• Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.
  
• Avoid the use of SELECT *. Instead, specify the required column names. This can prevent some Database Engine errors that stop stored procedure execution.
  
• Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Send just the essential data to the client application.
  
• Use explicit transactions by using BEGIN/END TRANSACTION and keep transactions as short as possible. Longer transactions mean longer record locking and a greater potential for deadlock.
  
• Use the T-SQL TRY…CATCH feature for error handling inside a procedure. TRY…CATCH can encapsulate an entire block of T-SQL statements. This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.
  
• Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE T-SQL statements in the body of the stored procedure. This will prevent passing NULL to columns that do not allow null values.
  
• Use NULL or NOT NULL for each column in a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.
  
• Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Be aware that in T-SQL, NULL is not an empty or “nothing” value. It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.
  
• Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.