SQL Server Stored Procedure Best Practices

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:

http://www.sqlmag.com/article/tsql3/should-i-use-the-sp_-prefix-for-proc...

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:

http://www.techrepublic.com/blog/datacenter/avoiding-cursors-with-sql-server-2005/412

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:

http://www.sqlmag.com/article/tsql3/should-i-use-the-sp_-prefix-for-proc...

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:

http://www.techrepublic.com/blog/datacenter/avoiding-cursors-with-sql-server-2005/412

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:

http://www.sqlmag.com/article/tsql3/should-i-use-the-sp_-prefix-for-proc...

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:

http://www.techrepublic.com/blog/datacenter/avoiding-cursors-with-sql-server-2005/412

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:

http://www.sqlmag.com/article/tsql3/should-i-use-the-sp_-prefix-for-proc...

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:

http://www.techrepublic.com/blog/datacenter/avoiding-cursors-with-sql-server-2005/412

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:

http://www.sqlmag.com/article/tsql3/should-i-use-the-sp_-prefix-for-proc...

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:

http://www.techrepublic.com/blog/datacenter/avoiding-cursors-with-sql-server-2005/412

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:

http://www.sqlmag.com/article/tsql3/should-i-use-the-sp_-prefix-for-proc...

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:

http://www.techrepublic.com/blog/datacenter/avoiding-cursors-with-sql-server-2005/412

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

Creating the base Web Part.

1. Create a new SharePoint 2010 Empty SharePoint Project. Name it MyWebPartProject.
2. Once it’s been created, click on the project, choose Add > New Item.
3. Choose the Visual Web Part template and name it MyWebPart.

We’ve now have a very simple Web part that we can build, deploy, and add to our pages in SharePoint. Of course, it wont show anything, but this it shows how simple it is to get a Web part skeleton setup to a compiled, deployable and usable state. Let me explain some of important files we’ll focus on:

• MyWebPartUserControl.ascx
  This UserControl files take care of all the visual front-end controls and functionality. It’s associated MyWebPartUserControl.ascx.cs file takes care of the code-behind work.
• MyWebPart.cs
  This file is the class that is used to handle all the behind-the-scenes functionality of your Web part. This where the Web Part is initialized, where it loads up, and references the UserControl.

All the other files that are in your project are necessary for the Web part to function, however you don’t need to touch any of other files to get a custom WebPart (even at a very customized level) working. For now, just focus on the MyWebPart.cs, MyWebPartUserControl.ascx and MyWebPartUserControl.ascx.cs files.

Add some functionality

1. Add an asp Label Control named lbl_MySelection to the MyWebPartUserControl.ascx file.
2. Open the MyWebPartUserControl.ascx.cs file and override the OnPreRender method like such and add the following code to it:
   protected override void OnPreRender(EventArgs e)
   {
      base.OnPreRender(e);
      lbl_MySelection.Text = “Please make a selection.”;
   }
3. Deploy the project to your SharePoint instance
4. Edit a page and add your Web part to the page

We should now see some text that says, “Please make a selection.” This demonstrates how and where to add custom code to interact with our UserControls’ form controls (in this case our lbl_MySelection label).

Web Part Properties

When you edit a Web part, and the Web part editor pops up in your browser, each one of those options are called properties. We can add our own properties and in real development environments they’re not a novelty but a necessity.
Before we begin with the code, it’s important to understand that there are two major categories to Web part properties:

• Standard Toolbox Properties
  These are standard text fields, checkboxes, or dropdown lists that are able to receive data from the user and push those values to the Webpart. These do not display dynamic data. For instance: You couldn’t use a standard dropdownlist property to display a list of items from a SharePoint list. This is because the there’s no way to bind data to the dropdownlist, it uses a hard-coded enum in your Web part .CS file (more on this in the example code below).
• Custom EditorPart Toolbox Properties
  These allow you to build up any type of asp control (or third party control) bind custom data to do (like list data from within SharePoint or an external database) and push these values to the Web part.

Creating Standard Toolbox Properties

1. Open up your MyWebPart.cs file.
2. Right before the private const string _ascxPath … declaration, add the following code:
   [WebBrowsable(true),
   Category("Miscellaneous"),
   Personalizable(PersonalizationScope.Shared),
   WebDisplayName("Enter some text")]
   public string CustomTextProp { get; set; }
   The Category seen here can be any existing category within the Web part editor tool pane, or you can type your own custom category. The WebDisplayName option is what the label will say above the property. And of course, the final line is the actual property.
   If we deploy our project now, we will see our property show up in the Web part tool pane when editing the Web part, but it simply is a dummy property area. It doesn’t do anything because we haven’t tied the property to our MyWebPartUserControl. Let’s do that now…
3. Open the MyWebPartUserControl.ascx.cs file and right before the empty Page_Load() method, add the following property:
   public MyWebPart WebPart { get; set; }
   This will allow us to reference our other MyWebPart.cs class in this ascx code-beind class and ultimately expose the tool pane properties to us.
4. Back in the MyWebPart.cs file, locate the CreateChildControls() method and replace it with the following:
   protected override void CreateChildControls()
   {
      MyWebPartUserControl control = Page.LoadControl(_ascxPath) as MyWebPartUserControl;
      if (control != null)
         control.WebPart = this;
         Controls.Add(control);
      }
   }
   This looks for our user control on the page and it’s not null, we set the WebPart property (which we created in the previous step). Then we add the usercontrol to the controls collection.
5. Switch back again to the MyWebPartUserControl.ascx.cs file. Locate the OnPreRender method and replace it with the following:
   protected override void OnPreRender(EventArgs e)
   {
      base.OnPreRender(e);
      if (this.WebPart != null && this.WebPart.CustomTextProp!=null)
      {
         lbl_MySelection.Text = this.WebPart.CustomTextProp.ToString();
      }
   }
   This is the final piece to the puzzle. We’re setting the text field within our Web parts’ user control to the tool pane’s property text field.

Of course, you can do more than just text field type properties (the following code would be implemented in your MyWebPart.cs file:

• Checkboxes
  [WebBrowsable(true),
  Category("Miscellaneous"),
  Personalizable(PersonalizationScope.Shared),
  WebDisplayName("Checkbox Option Text")]
  public bool CustomCheckboxProp { get; set; }
• Dropdown Lists
  public enum ddlEnum { option1, option2, option3 }
  [WebBrowsable(true),
  Category("Miscellaneous"),
  Personalizable(PersonalizationScope.Shared),
  WebDisplayName("Dropdown List Display Text")]
  public ddlEnum ddlProp { get; set; }

As you can see so far, the standard tool pane properties are kind of limited; especially the dropdown list: we’re tied to the enumeration. What if we wanted some SharePoint list data feeding that dropdownlist Control? To create fully customized tool pane properties, we need to create our own “EditorPart” and here’s how to do it:

Creating custom EditorPart properties

We’re going to create our own custom DropDownList control EditorPart that feeds in some values from a custom SharePoint list.

1. In your root SharePoint site, create a new custom list. You can keep the new custom list really simple, or you can add your own custom fields; doesn’t matter. If you’d like to use a already existing list, feel free to reference that list instead.
2. Open your MyWebPart.cs file and after the private const string _ascxPath declaration add the following code:
   public string _OptionValue { get; set; }
3. After the whole MyWebPart.cs class declaration, add the following class:
   public class MyEditorPart : EditorPart
   {
      private DropDownList ddl_Options;
      protected override void CreateChildControls()
      {
         base.CreateChildControls();
      }
      public override bool ApplyChanges()
      {
         EnsureChildControls();
         MyWebPart webPart = WebPartToEdit as MyWebPart;
         if (webPart != null)
            webPart._OptionValue=ddl_Panels.SelectedValue.ToString();
         return true;
      }
      public override void SyncChanges()
      {
         EnsureChildControls();
         MyWebPart webPart = WebPartToEdit as MyWebPart;
         if (webPart != null)
            ddl_Options.SelectedValue = webPart._OptionValue .ToString();
      }
   }
   This is the meat and potatoes, if you will, of what makes the custom EditorPart a reality. But there is some more tweaking to do in order for it all to work:
4. Locate the CreateChildControls() method you created in the code in the previous step. Replace what’s inside with the following:
   base.CreateChildControls();
   ddl_Options = new DropDownList();
   lbl_Options = new Label();
   lbl_Options.Text = “<strong>Choose:</strong><br />:”;
   using (SPSite site = new SPSite(“http://” + Page.Request.Url.Host.ToString()))
   {
      using (SPWeb web = site.OpenWeb())
      {
         SPList Forms = web.Lists["Side Panel Content"];
         SPQuery qry = new SPQuery();
         qry.Query = @”<Query><Where><Gt><FieldRef Name=’ID’ /><Value Type=’Counter’>0</Value></Gt></Where></Query>”;
         SPListItemCollection SPLIC = Forms.GetItems(qry);
         if (SPLIC != null && SPLIC.Count > 0)
         {
            foreach (SPListItem SPLI in SPLIC)
            {
               string OptionTitle = “”;
               string OptionID = “”;
               if (SPLI["ID"] != null)
                  PanelID = SPLI["ID"].ToString();
               if (SPLI["Title"] != null)
                  PanelTitle = SPLI["Title"].ToString();
               if (PanelTitle != “” && PanelID != “”)
                  ddl_Options.Items.Add(new ListItem(OptionTitle, OptionID));
            }
         }
      }
   }
   Controls.Add(lbl_Panels);
   Controls.Add(ddl_Panels);
5. Now, back in your MyWebPartUserControl.ascx.cs file, add the following line of code to your OnPreRender() method:
   lbl_MySelection.Text = WebPart._OptionValue;
   This will set the label in your Web part’s User Control to the value of the DropDown list in your custom EditorPart tool pane property.