Stored Procedures Best Practices:
Today we will check which performance parameters and other point we should keep in mind while creating a stored procedure.
There are many benefits of using Stored procedures. basically a stored procedure is a set of SQL statements which are stored in database in compiled form with a specific name and most important benefit is, we can use this stored procedure in number of programs, with this feature we are not required to write query again and again.
we can also perform many operations at database level so it will not load our application and application will perform well. we can use many programming concept like IF-ELSE, WHILE loop and other constructs also.
Here i am going to share some good approaches while creating a stored procedure which, a developer should use to enhance the performance of stored procedure. there are some standard rules and signature to create stored procedure but we can add other things as per our requirements. i will not go to details of each benefit, will give a short description. so let’s have a look:
1. SET NOCOUNT ON: we should always use SET NOCOUNT ON statement at the start of your stored procedure code to minimize the unnecessary network round trips.
2. PARAMETER SNIFFING: This is a very good concept to study, we should never use our stored procedure parameters directly in SQL statement because it will cause Parameter Sniffing. first, we should assign the parameter to local variable and then we should pass to SQL statements.
3. Use table variables: Table Variables are very good for small data instead of temp table. it will give your stored procedure a good rise in performance.
4. Use of Temp Tables: Temp tables are not always recommended but if it is required and no other option then always create indexes on temp table. it is for big data like millions of records in this case do not use table variables.
5. Avoid using CURSORS: CURSORS are used to manipulate the data row-by-row but it will make your Stored procedure very slow, always try to avoid CURSORS and check for other SET based approaches.
6. Use TRY-CATCH block: This is a very good approach to use TRY-CATCH in SQL statement so if there is any error you can handle at Database and return a proper flag to application. it will also never let your application do any operation and check data for exception handling.
7. Using Transactions: BEGIB TRANSACTION, COMMIT/ROLLBACK these are good to use when you are going to perform some action on your database but always try to use short transaction with them otherwise it will create blocking in database and deadlocks. but by using Transactions we can keep track of successful/unsuccessful of your transaction.
8. Aliasing objects: although database will allow you to use SQL reserve keyword but try to avoid use reserved keywords otherwise it will affect your statement performance and its good approach to use meaningful naming conventions.
9. Avoid GOTO Statement: Like all other programming language its bad practice to use GOTO statement. it good to use and filter you statements with IF-ELSE without using GOTO.
10. Avoid 'sp_' prefix: your Stored procedure should not start with 'sp_' because in this case compiler will go first to master database and then it will come back to current database so it will slow down your Stored procedure.
11. Use fully qualified objects name: In SQL queries, it will help the compiler to find cached and compiled object quickly.
12. Use WITH ENCRYPTION: if it is required you can use this to hide the Stored Procedure statement.
13. Add Comments: always add comments in your procedure like a good programmer.
14. Use BEGIN-END code block: Whole Stored procedure should be in BEGIN-END block.
Below is the standard template for creating stored procedure which is generated by SQL Management studio.
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Comments and Suggestions are Always Welcome!
