Below are some good practices for creating stored procedures in SQL Server database.
1) Include SET NOCOUNT ON Statement in your stored procedure
Reason:
With every select or DML statement, SQL server returns text message on number of rows affected by the statement expectation. This might be needed when developing and debugging the procedure but once the procedure is completed then this won’t be in any use by the system.
So to avoid the additional processing time by the SQL Server to display such messages, use SET NOCOUNT ON statement
Benefit:
Avoid unnecessary processing load and performance improvement of the stored procedure
How?:
CREATE PROCEDURE P_MyProcedureName
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>
-- reset SET NCOUNT to off
SET NOCOUNT OFF;
END
GO
|
2) Use Schema name with tables objects in the stored procedure
Reason:
This prevents the database engine from checking for an object under the specified schema first, and also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas
Benefit:
This help in directly finding the complied plan instead of searching the objects in other possible schema
How?:
SELECT * FROM dbo.TableName -- Preferred method
SELECT * FROM TableName -- Avoid this method
EXEC dbo.P_MyProcedureName -- Preferred method
EXEC P_MyProcedureName -- Avoid this method
|
3) Do not use SP_ prefix to name stored procedure
Reason:
Stored procedure having prefix SP_ causes some performance penalty, because SQL Server first search it in the master database and then in current database
Reference:
4) Use SP_EXECUTESQL instead EXECUTE
Reason:
The SP_EXECUTESQL stored procedure supports parameters, so it can be used for passing the SQL query and all the parameters used with in the query, this will create execution plan for the query batch which can be reused for different parameters passed to it
Benefit:
Improve the reusability of the execution plan for the dynamic SQL
How?:
-- Avoid this
DECLARE @CustomerQuery VARCHAR(512)
DECLARE @CustomerID INT
SET @CustomerID = 2
SET @CustomerQuery = 'SELECT * FROM dbo.Customers WHERE CustomerID = ' + CONVERT(VARCHAR(3),@CustomerID)
EXEC (@CustomerQuery)
-- Use this
DECLARE @CustomerQuery NVARCHAR(512)
SET @CustomerQuery = N'SELECT * FROM dbo.Customers WHERE @CustomerID = @CustomerID'
EXECUTE sp_executesql @CustomerQuery, N'@@CustomerID int',@CustomerID = 25
|
5) Avoid cursors in your stored procedures
Reason:
Cursors take more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time, it uses a lot of resources for overhead processing to maintain current record position in a record set and this decreases the performance,
Reference:
6) Use @table variable instead temp table for short data size
There is no universal rule of when and where to use temporary tables or table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense.
A table variable is created in memory, and so performs slightly better than #temp tables
7) Keep transactions as short as possible
A lengthy transaction introduces locking, if locking happens for a longer time it introduces blocking and which leads to deadlock if the transaction is lengthy
8) Use Try catch block for transaction handling
BEGIN TRY
BEGIN TRANSACTION
-- SQL queries
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--Error handling code
END CATCH
|
9) Use proper & consistent naming convention for naming the stored procedure and lining up the parameters and data types
If the stored procedure is properly named and well formatted then it becomes easier doing for debugging and review.
You can use some prefix for you stored procedure (just make sure not to use SP_ prefix as explained above), whatever prefix you are using, use it consistently, below is an example showing the naming of the procedure and formatting.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_Customer_Insert
@Param1 int,
@Param2 int
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 Col1
Col2,
Col3
FROM TableA
WHERE Col1 = 'x'
SET NOCOUNT OFF;
END
GO
|
10) Use upper casing for T-SQL keywords and built-in functions
Using capital casing for the keyword improves the readability of the procedure, e.g. you can use CREATE PROCEDURE instead Create Procedure, use SELECT instead Select, it is just to improve the readability of your procedure other than that I don’t see any reason for it.
11) Avoid data type prefix in variable declaration
There are many developers when they declare some variable they put the data type prefix in the variable name, e.g.
DECLARE @varCustomerQuery VARCHAR(512)
Using the data type prefix may help you knowing the data type by looking at the variable name, but what if you have to change the data type of the variable (in above example VARCHAR to NVARCHAR) then not only you have to change all the variable/parameter declarations but you also have to change @varCustomerQuery to@nvarCustomerQuery in all the places wherever you have used this variable in the stored procedure
12) Use BEGIN END and Go syntax to complete the batch of a stored procedure
13) Use single declare for local variables and single select for variable value assignment
-- Avoid this
DECLARE @var1 INT;
DECLARE @var2 INT;
DECLARE @var3 VARCHAR(10);
-- Use this
DECLARE @varX INT,
@varY INT,
@varZ VARCHAR(10);
-- If you want to assign some default value, you use this
DECLARE @varP INT = 1,
@varQ INT = 2,
@varR VARCHAR(10) = 'X';
|
14) Use result set or output parameter for returning some data from stored procedure
15) Use proper parenthesis when using logical operators in where clause
Below are some good practices for creating stored procedures in SQL Server database.
1) Include SET NOCOUNT ON Statement in your stored procedure
Reason:
With every select or DML statement, SQL server returns text message on number of rows affected by the statement expectation. This might be needed when developing and debugging the procedure but once the procedure is completed then this won’t be in any use by the system.
So to avoid the additional processing time by the SQL Server to display such messages, use SET NOCOUNT ON statement
Benefit:
Avoid unnecessary processing load and performance improvement of the stored procedure
How?:
CREATE PROCEDURE P_MyProcedureName
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>
-- reset SET NCOUNT to off
SET NOCOUNT OFF;
END
GO
|
2) Use Schema name with tables objects in the stored procedure
Reason:
This prevents the database engine from checking for an object under the specified schema first, and also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas
Benefit:
This help in directly finding the complied plan instead of searching the objects in other possible schema
How?:
SELECT * FROM dbo.TableName -- Preferred method
SELECT * FROM TableName -- Avoid this method
EXEC dbo.P_MyProcedureName -- Preferred method
EXEC P_MyProcedureName -- Avoid this method
|
3) Do not use SP_ prefix to name stored procedure
Reason:
Stored procedure having prefix SP_ causes some performance penalty, because SQL Server first search it in the master database and then in current database
Reference:
4) Use SP_EXECUTESQL instead EXECUTE
Reason:
The SP_EXECUTESQL stored procedure supports parameters, so it can be used for passing the SQL query and all the parameters used with in the query, this will create execution plan for the query batch which can be reused for different parameters passed to it
Benefit:
Improve the reusability of the execution plan for the dynamic SQL
How?:
-- Avoid this
DECLARE @CustomerQuery VARCHAR(512)
DECLARE @CustomerID INT
SET @CustomerID = 2
SET @CustomerQuery = 'SELECT * FROM dbo.Customers WHERE CustomerID = ' + CONVERT(VARCHAR(3),@CustomerID)
EXEC (@CustomerQuery)
-- Use this
DECLARE @CustomerQuery NVARCHAR(512)
SET @CustomerQuery = N'SELECT * FROM dbo.Customers WHERE @CustomerID = @CustomerID'
EXECUTE sp_executesql @CustomerQuery, N'@@CustomerID int',@CustomerID = 25
|
5) Avoid cursors in your stored procedures
Reason:
Cursors take more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time, it uses a lot of resources for overhead processing to maintain current record position in a record set and this decreases the performance,
Reference:
6) Use @table variable instead temp table for short data size
There is no universal rule of when and where to use temporary tables or table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense.
A table variable is created in memory, and so performs slightly better than #temp tables
7) Keep transactions as short as possible
A lengthy transaction introduces locking, if locking happens for a longer time it introduces blocking and which leads to deadlock if the transaction is lengthy
8) Use Try catch block for transaction handling
BEGIN TRY
BEGIN TRANSACTION
-- SQL queries
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--Error handling code
END CATCH
|
9) Use proper & consistent naming convention for naming the stored procedure and lining up the parameters and data types
If the stored procedure is properly named and well formatted then it becomes easier doing for debugging and review.
You can use some prefix for you stored procedure (just make sure not to use SP_ prefix as explained above), whatever prefix you are using, use it consistently, below is an example showing the naming of the procedure and formatting.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_Customer_Insert
@Param1 int,
@Param2 int
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 Col1
Col2,
Col3
FROM TableA
WHERE Col1 = 'x'
SET NOCOUNT OFF;
END
GO
|
10) Use upper casing for T-SQL keywords and built-in functions
Using capital casing for the keyword improves the readability of the procedure, e.g. you can use CREATE PROCEDURE instead Create Procedure, use SELECT instead Select, it is just to improve the readability of your procedure other than that I don’t see any reason for it.
11) Avoid data type prefix in variable declaration
There are many developers when they declare some variable they put the data type prefix in the variable name, e.g.
DECLARE @varCustomerQuery VARCHAR(512)
Using the data type prefix may help you knowing the data type by looking at the variable name, but what if you have to change the data type of the variable (in above example VARCHAR to NVARCHAR) then not only you have to change all the variable/parameter declarations but you also have to change @varCustomerQuery to@nvarCustomerQuery in all the places wherever you have used this variable in the stored procedure
12) Use BEGIN END and Go syntax to complete the batch of a stored procedure
13) Use single declare for local variables and single select for variable value assignment
-- Avoid this
DECLARE @var1 INT;
DECLARE @var2 INT;
DECLARE @var3 VARCHAR(10);
-- Use this
DECLARE @varX INT,
@varY INT,
@varZ VARCHAR(10);
-- If you want to assign some default value, you use this
DECLARE @varP INT = 1,
@varQ INT = 2,
@varR VARCHAR(10) = 'X';
|
14) Use result set or output parameter for returning some data from stored procedure
15) Use proper parenthesis when using logical operators in where clause
Below are some good practices for creating stored procedures in SQL Server database.
1) Include SET NOCOUNT ON Statement in your stored procedure
Reason:
With every select or DML statement, SQL server returns text message on number of rows affected by the statement expectation. This might be needed when developing and debugging the procedure but once the procedure is completed then this won’t be in any use by the system.
So to avoid the additional processing time by the SQL Server to display such messages, use SET NOCOUNT ON statement
Benefit:
Avoid unnecessary processing load and performance improvement of the stored procedure
How?:
CREATE PROCEDURE P_MyProcedureName
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>
-- reset SET NCOUNT to off
SET NOCOUNT OFF;
END
GO
|
2) Use Schema name with tables objects in the stored procedure
Reason:
This prevents the database engine from checking for an object under the specified schema first, and also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas
Benefit:
This help in directly finding the complied plan instead of searching the objects in other possible schema
How?:
SELECT * FROM dbo.TableName -- Preferred method
SELECT * FROM TableName -- Avoid this method
EXEC dbo.P_MyProcedureName -- Preferred method
EXEC P_MyProcedureName -- Avoid this method
|
3) Do not use SP_ prefix to name stored procedure
Reason:
Stored procedure having prefix SP_ causes some performance penalty, because SQL Server first search it in the master database and then in current database
Reference:
4) Use SP_EXECUTESQL instead EXECUTE
Reason:
The SP_EXECUTESQL stored procedure supports parameters, so it can be used for passing the SQL query and all the parameters used with in the query, this will create execution plan for the query batch which can be reused for different parameters passed to it
Benefit:
Improve the reusability of the execution plan for the dynamic SQL
How?:
-- Avoid this
DECLARE @CustomerQuery VARCHAR(512)
DECLARE @CustomerID INT
SET @CustomerID = 2
SET @CustomerQuery = 'SELECT * FROM dbo.Customers WHERE CustomerID = ' + CONVERT(VARCHAR(3),@CustomerID)
EXEC (@CustomerQuery)
-- Use this
DECLARE @CustomerQuery NVARCHAR(512)
SET @CustomerQuery = N'SELECT * FROM dbo.Customers WHERE @CustomerID = @CustomerID'
EXECUTE sp_executesql @CustomerQuery, N'@@CustomerID int',@CustomerID = 25
|
5) Avoid cursors in your stored procedures
Reason:
Cursors take more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time, it uses a lot of resources for overhead processing to maintain current record position in a record set and this decreases the performance,
Reference:
6) Use @table variable instead temp table for short data size
There is no universal rule of when and where to use temporary tables or table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense.
A table variable is created in memory, and so performs slightly better than #temp tables
7) Keep transactions as short as possible
A lengthy transaction introduces locking, if locking happens for a longer time it introduces blocking and which leads to deadlock if the transaction is lengthy
8) Use Try catch block for transaction handling
BEGIN TRY
BEGIN TRANSACTION
-- SQL queries
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--Error handling code
END CATCH
|
9) Use proper & consistent naming convention for naming the stored procedure and lining up the parameters and data types
If the stored procedure is properly named and well formatted then it becomes easier doing for debugging and review.
You can use some prefix for you stored procedure (just make sure not to use SP_ prefix as explained above), whatever prefix you are using, use it consistently, below is an example showing the naming of the procedure and formatting.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_Customer_Insert
@Param1 int,
@Param2 int
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 Col1
Col2,
Col3
FROM TableA
WHERE Col1 = 'x'
SET NOCOUNT OFF;
END
GO
|
10) Use upper casing for T-SQL keywords and built-in functions
Using capital casing for the keyword improves the readability of the procedure, e.g. you can use CREATE PROCEDURE instead Create Procedure, use SELECT instead Select, it is just to improve the readability of your procedure other than that I don’t see any reason for it.
11) Avoid data type prefix in variable declaration
There are many developers when they declare some variable they put the data type prefix in the variable name, e.g.
DECLARE @varCustomerQuery VARCHAR(512)
Using the data type prefix may help you knowing the data type by looking at the variable name, but what if you have to change the data type of the variable (in above example VARCHAR to NVARCHAR) then not only you have to change all the variable/parameter declarations but you also have to change @varCustomerQuery to@nvarCustomerQuery in all the places wherever you have used this variable in the stored procedure
12) Use BEGIN END and Go syntax to complete the batch of a stored procedure
13) Use single declare for local variables and single select for variable value assignment
-- Avoid this
DECLARE @var1 INT;
DECLARE @var2 INT;
DECLARE @var3 VARCHAR(10);
-- Use this
DECLARE @varX INT,
@varY INT,
@varZ VARCHAR(10);
-- If you want to assign some default value, you use this
DECLARE @varP INT = 1,
@varQ INT = 2,
@varR VARCHAR(10) = 'X';
|
14) Use result set or output parameter for returning some data from stored procedure
15) Use proper parenthesis when using logical operators in where clause
Below are some good practices for creating stored procedures in SQL Server database.
1) Include SET NOCOUNT ON Statement in your stored procedure
Reason:
With every select or DML statement, SQL server returns text message on number of rows affected by the statement expectation. This might be needed when developing and debugging the procedure but once the procedure is completed then this won’t be in any use by the system.
So to avoid the additional processing time by the SQL Server to display such messages, use SET NOCOUNT ON statement
Benefit:
Avoid unnecessary processing load and performance improvement of the stored procedure
How?:
CREATE PROCEDURE P_MyProcedureName
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>
-- reset SET NCOUNT to off
SET NOCOUNT OFF;
END
GO
|
2) Use Schema name with tables objects in the stored procedure
Reason:
This prevents the database engine from checking for an object under the specified schema first, and also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas
Benefit:
This help in directly finding the complied plan instead of searching the objects in other possible schema
How?:
SELECT * FROM dbo.TableName -- Preferred method
SELECT * FROM TableName -- Avoid this method
EXEC dbo.P_MyProcedureName -- Preferred method
EXEC P_MyProcedureName -- Avoid this method
|
3) Do not use SP_ prefix to name stored procedure
Reason:
Stored procedure having prefix SP_ causes some performance penalty, because SQL Server first search it in the master database and then in current database
Reference:
4) Use SP_EXECUTESQL instead EXECUTE
Reason:
The SP_EXECUTESQL stored procedure supports parameters, so it can be used for passing the SQL query and all the parameters used with in the query, this will create execution plan for the query batch which can be reused for different parameters passed to it
Benefit:
Improve the reusability of the execution plan for the dynamic SQL
How?:
-- Avoid this
DECLARE @CustomerQuery VARCHAR(512)
DECLARE @CustomerID INT
SET @CustomerID = 2
SET @CustomerQuery = 'SELECT * FROM dbo.Customers WHERE CustomerID = ' + CONVERT(VARCHAR(3),@CustomerID)
EXEC (@CustomerQuery)
-- Use this
DECLARE @CustomerQuery NVARCHAR(512)
SET @CustomerQuery = N'SELECT * FROM dbo.Customers WHERE @CustomerID = @CustomerID'
EXECUTE sp_executesql @CustomerQuery, N'@@CustomerID int',@CustomerID = 25
|
5) Avoid cursors in your stored procedures
Reason:
Cursors take more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time, it uses a lot of resources for overhead processing to maintain current record position in a record set and this decreases the performance,
Reference:
6) Use @table variable instead temp table for short data size
There is no universal rule of when and where to use temporary tables or table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense.
A table variable is created in memory, and so performs slightly better than #temp tables
7) Keep transactions as short as possible
A lengthy transaction introduces locking, if locking happens for a longer time it introduces blocking and which leads to deadlock if the transaction is lengthy
8) Use Try catch block for transaction handling
BEGIN TRY
BEGIN TRANSACTION
-- SQL queries
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--Error handling code
END CATCH
|
9) Use proper & consistent naming convention for naming the stored procedure and lining up the parameters and data types
If the stored procedure is properly named and well formatted then it becomes easier doing for debugging and review.
You can use some prefix for you stored procedure (just make sure not to use SP_ prefix as explained above), whatever prefix you are using, use it consistently, below is an example showing the naming of the procedure and formatting.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_Customer_Insert
@Param1 int,
@Param2 int
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 Col1
Col2,
Col3
FROM TableA
WHERE Col1 = 'x'
SET NOCOUNT OFF;
END
GO
|
10) Use upper casing for T-SQL keywords and built-in functions
Using capital casing for the keyword improves the readability of the procedure, e.g. you can use CREATE PROCEDURE instead Create Procedure, use SELECT instead Select, it is just to improve the readability of your procedure other than that I don’t see any reason for it.
11) Avoid data type prefix in variable declaration
There are many developers when they declare some variable they put the data type prefix in the variable name, e.g.
DECLARE @varCustomerQuery VARCHAR(512)
Using the data type prefix may help you knowing the data type by looking at the variable name, but what if you have to change the data type of the variable (in above example VARCHAR to NVARCHAR) then not only you have to change all the variable/parameter declarations but you also have to change @varCustomerQuery to@nvarCustomerQuery in all the places wherever you have used this variable in the stored procedure
12) Use BEGIN END and Go syntax to complete the batch of a stored procedure
13) Use single declare for local variables and single select for variable value assignment
-- Avoid this
DECLARE @var1 INT;
DECLARE @var2 INT;
DECLARE @var3 VARCHAR(10);
-- Use this
DECLARE @varX INT,
@varY INT,
@varZ VARCHAR(10);
-- If you want to assign some default value, you use this
DECLARE @varP INT = 1,
@varQ INT = 2,
@varR VARCHAR(10) = 'X';
|
14) Use result set or output parameter for returning some data from stored procedure
15) Use proper parenthesis when using logical operators in where clause
Below are some good practices for creating stored procedures in SQL Server database.
1) Include SET NOCOUNT ON Statement in your stored procedure
Reason:
With every select or DML statement, SQL server returns text message on number of rows affected by the statement expectation. This might be needed when developing and debugging the procedure but once the procedure is completed then this won’t be in any use by the system.
So to avoid the additional processing time by the SQL Server to display such messages, use SET NOCOUNT ON statement
Benefit:
Avoid unnecessary processing load and performance improvement of the stored procedure
How?:
CREATE PROCEDURE P_MyProcedureName
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>
-- reset SET NCOUNT to off
SET NOCOUNT OFF;
END
GO
|
2) Use Schema name with tables objects in the stored procedure
Reason:
This prevents the database engine from checking for an object under the specified schema first, and also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas
Benefit:
This help in directly finding the complied plan instead of searching the objects in other possible schema
How?:
SELECT * FROM dbo.TableName -- Preferred method
SELECT * FROM TableName -- Avoid this method
EXEC dbo.P_MyProcedureName -- Preferred method
EXEC P_MyProcedureName -- Avoid this method
|
3) Do not use SP_ prefix to name stored procedure
Reason:
Stored procedure having prefix SP_ causes some performance penalty, because SQL Server first search it in the master database and then in current database
Reference:
4) Use SP_EXECUTESQL instead EXECUTE
Reason:
The SP_EXECUTESQL stored procedure supports parameters, so it can be used for passing the SQL query and all the parameters used with in the query, this will create execution plan for the query batch which can be reused for different parameters passed to it
Benefit:
Improve the reusability of the execution plan for the dynamic SQL
How?:
-- Avoid this
DECLARE @CustomerQuery VARCHAR(512)
DECLARE @CustomerID INT
SET @CustomerID = 2
SET @CustomerQuery = 'SELECT * FROM dbo.Customers WHERE CustomerID = ' + CONVERT(VARCHAR(3),@CustomerID)
EXEC (@CustomerQuery)
-- Use this
DECLARE @CustomerQuery NVARCHAR(512)
SET @CustomerQuery = N'SELECT * FROM dbo.Customers WHERE @CustomerID = @CustomerID'
EXECUTE sp_executesql @CustomerQuery, N'@@CustomerID int',@CustomerID = 25
|
5) Avoid cursors in your stored procedures
Reason:
Cursors take more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time, it uses a lot of resources for overhead processing to maintain current record position in a record set and this decreases the performance,
Reference:
6) Use @table variable instead temp table for short data size
There is no universal rule of when and where to use temporary tables or table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense.
A table variable is created in memory, and so performs slightly better than #temp tables
7) Keep transactions as short as possible
A lengthy transaction introduces locking, if locking happens for a longer time it introduces blocking and which leads to deadlock if the transaction is lengthy
8) Use Try catch block for transaction handling
BEGIN TRY
BEGIN TRANSACTION
-- SQL queries
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--Error handling code
END CATCH
|
9) Use proper & consistent naming convention for naming the stored procedure and lining up the parameters and data types
If the stored procedure is properly named and well formatted then it becomes easier doing for debugging and review.
You can use some prefix for you stored procedure (just make sure not to use SP_ prefix as explained above), whatever prefix you are using, use it consistently, below is an example showing the naming of the procedure and formatting.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_Customer_Insert
@Param1 int,
@Param2 int
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 Col1
Col2,
Col3
FROM TableA
WHERE Col1 = 'x'
SET NOCOUNT OFF;
END
GO
|
10) Use upper casing for T-SQL keywords and built-in functions
Using capital casing for the keyword improves the readability of the procedure, e.g. you can use CREATE PROCEDURE instead Create Procedure, use SELECT instead Select, it is just to improve the readability of your procedure other than that I don’t see any reason for it.
11) Avoid data type prefix in variable declaration
There are many developers when they declare some variable they put the data type prefix in the variable name, e.g.
DECLARE @varCustomerQuery VARCHAR(512)
Using the data type prefix may help you knowing the data type by looking at the variable name, but what if you have to change the data type of the variable (in above example VARCHAR to NVARCHAR) then not only you have to change all the variable/parameter declarations but you also have to change @varCustomerQuery to@nvarCustomerQuery in all the places wherever you have used this variable in the stored procedure
12) Use BEGIN END and Go syntax to complete the batch of a stored procedure
13) Use single declare for local variables and single select for variable value assignment
-- Avoid this
DECLARE @var1 INT;
DECLARE @var2 INT;
DECLARE @var3 VARCHAR(10);
-- Use this
DECLARE @varX INT,
@varY INT,
@varZ VARCHAR(10);
-- If you want to assign some default value, you use this
DECLARE @varP INT = 1,
@varQ INT = 2,
@varR VARCHAR(10) = 'X';
|
14) Use result set or output parameter for returning some data from stored procedure
15) Use proper parenthesis when using logical operators in where clause
Below are some good practices for creating stored procedures in SQL Server database.
1) Include SET NOCOUNT ON Statement in your stored procedure
Reason:
With every select or DML statement, SQL server returns text message on number of rows affected by the statement expectation. This might be needed when developing and debugging the procedure but once the procedure is completed then this won’t be in any use by the system.
So to avoid the additional processing time by the SQL Server to display such messages, use SET NOCOUNT ON statement
Benefit:
Avoid unnecessary processing load and performance improvement of the stored procedure
How?:
CREATE PROCEDURE P_MyProcedureName
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>
-- reset SET NCOUNT to off
SET NOCOUNT OFF;
END
GO
|
2) Use Schema name with tables objects in the stored procedure
Reason:
This prevents the database engine from checking for an object under the specified schema first, and also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas
Benefit:
This help in directly finding the complied plan instead of searching the objects in other possible schema
How?:
SELECT * FROM dbo.TableName -- Preferred method
SELECT * FROM TableName -- Avoid this method
EXEC dbo.P_MyProcedureName -- Preferred method
EXEC P_MyProcedureName -- Avoid this method
|
3) Do not use SP_ prefix to name stored procedure
Reason:
Stored procedure having prefix SP_ causes some performance penalty, because SQL Server first search it in the master database and then in current database
Reference:
4) Use SP_EXECUTESQL instead EXECUTE
Reason:
The SP_EXECUTESQL stored procedure supports parameters, so it can be used for passing the SQL query and all the parameters used with in the query, this will create execution plan for the query batch which can be reused for different parameters passed to it
Benefit:
Improve the reusability of the execution plan for the dynamic SQL
How?:
-- Avoid this
DECLARE @CustomerQuery VARCHAR(512)
DECLARE @CustomerID INT
SET @CustomerID = 2
SET @CustomerQuery = 'SELECT * FROM dbo.Customers WHERE CustomerID = ' + CONVERT(VARCHAR(3),@CustomerID)
EXEC (@CustomerQuery)
-- Use this
DECLARE @CustomerQuery NVARCHAR(512)
SET @CustomerQuery = N'SELECT * FROM dbo.Customers WHERE @CustomerID = @CustomerID'
EXECUTE sp_executesql @CustomerQuery, N'@@CustomerID int',@CustomerID = 25
|
5) Avoid cursors in your stored procedures
Reason:
Cursors take more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time, it uses a lot of resources for overhead processing to maintain current record position in a record set and this decreases the performance,
Reference:
6) Use @table variable instead temp table for short data size
There is no universal rule of when and where to use temporary tables or table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense.
A table variable is created in memory, and so performs slightly better than #temp tables
7) Keep transactions as short as possible
A lengthy transaction introduces locking, if locking happens for a longer time it introduces blocking and which leads to deadlock if the transaction is lengthy
8) Use Try catch block for transaction handling
BEGIN TRY
BEGIN TRANSACTION
-- SQL queries
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--Error handling code
END CATCH
|
9) Use proper & consistent naming convention for naming the stored procedure and lining up the parameters and data types
If the stored procedure is properly named and well formatted then it becomes easier doing for debugging and review.
You can use some prefix for you stored procedure (just make sure not to use SP_ prefix as explained above), whatever prefix you are using, use it consistently, below is an example showing the naming of the procedure and formatting.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_Customer_Insert
@Param1 int,
@Param2 int
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 Col1
Col2,
Col3
FROM TableA
WHERE Col1 = 'x'
SET NOCOUNT OFF;
END
GO
|
10) Use upper casing for T-SQL keywords and built-in functions
Using capital casing for the keyword improves the readability of the procedure, e.g. you can use CREATE PROCEDURE instead Create Procedure, use SELECT instead Select, it is just to improve the readability of your procedure other than that I don’t see any reason for it.
11) Avoid data type prefix in variable declaration
There are many developers when they declare some variable they put the data type prefix in the variable name, e.g.
DECLARE @varCustomerQuery VARCHAR(512)
Using the data type prefix may help you knowing the data type by looking at the variable name, but what if you have to change the data type of the variable (in above example VARCHAR to NVARCHAR) then not only you have to change all the variable/parameter declarations but you also have to change @varCustomerQuery to@nvarCustomerQuery in all the places wherever you have used this variable in the stored procedure
12) Use BEGIN END and Go syntax to complete the batch of a stored procedure
13) Use single declare for local variables and single select for variable value assignment
-- Avoid this
DECLARE @var1 INT;
DECLARE @var2 INT;
DECLARE @var3 VARCHAR(10);
-- Use this
DECLARE @varX INT,
@varY INT,
@varZ VARCHAR(10);
-- If you want to assign some default value, you use this
DECLARE @varP INT = 1,
@varQ INT = 2,
@varR VARCHAR(10) = 'X';
|
14) Use result set or output parameter for returning some data from stored procedure
15) Use proper parenthesis when using logical operators in where clause
No comments:
Post a Comment