Best Practice to write SQL - Stored Procedure.

Hi Friends,

As every application has a Database, and some objects which are helping us to perform the actions on our Data, here I am going to tell Stored Procedure SQL Object related best practices.

• Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. That is, place it just after the AS keyword.

• 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.

• 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 behaviour is like row-based processing and degrades performance. 

• Avoid the use of SELECT *. Instead, specify the required column names.

• 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 deadlocking.

• Use the Transact- SQL TRY…CATCH feature for error handling inside a procedure.

• Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the 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.

• 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.

hope it will help to increase your application performance.