SET NOCOUNT (Transact-SQL)

Virendradugar
Posted by in Sql Server category on for Beginner level | Views : 14153 red flag
Rating: 4 out of 5  
 1 vote(s)

This article explains the SET NOCOUNT keyword's importance.
Introduction

Whenever we write any procedure and execute it a message appears in message window that shows no of rows affected with the statement written in the procedure and we become very happy to see that our procedure is working. 

But do you know that this message creates an extra overhead on the network? Yes it does.

By removing this extra overhead from the network, we can actually improve the performance of our database and our application.

How should we do it?

Solution

When you create any procedure then first line of your procedure should be
SET NOCOUNT ON;

This one line of code turns off the message that SQL server sends back to front end after every T-SQL statement is executed. This is applied for all SELECT, INSERT, UPDATE and DELETE statements. As when stored procedures are executed there is no need to pass this information back to front end.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.If we still need to get the count of no of rows affected, we can still use @@ROWCOUNT option. Because The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Template used in SQL SERVER 2005

================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: 
– Create date: 
– Description: 
– =============================================
CREATE PROCEDURE 
– Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = ,
<@Param2, sysname, @p2> = 
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
================================================

Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.


Conclusion

So next time, when you create any procedure, don't forget to include "SET NOCOUNT ON;". Otherwise, the best pratice is to get the stored procedure template from the SQL Server itself.

Enjoy..

Page copy protected against web site content infringement by Copyscape

About the Author

Virendradugar
Full Name: Virendra Dugar
Member Level: Silver
Member Status: Member,MVP
Member Since: 8/11/2009 4:14:05 AM
Country: India

http://jquerybyexample.blogspot.com
Virendra Dugar is experienced Senior Software Developer with over 5 years of hands-on experience working with Microsoft .NET technology (ASP.NET, C#, VB.NET,SQL Server). He is always keen to learn new technology. He holds a Master's Degree in Computer Application & Information technology from Gujarat University in india.In free time, he loves to listen music, read books, play games and do blogging etc. Visit his blogs : http://jquerybyexample.blogspot.com

Login to vote for this post.

Comments or Responses

Posted by: S.faizaan on: 7/11/2011 | Points: 25
nicely explained!!!
regards.

Login to post response

Comment using Facebook(Author doesn't get notification)