What is SET NOCOUNT ?

 I have divided this article in mainly three sections to understand you-

    What- Definition

    Why- Advantages

    How- Inplementation

     I have used sample 'adventureworks' database for example.'Adventureworks' database is easily available to download from microsoft site.

What is SET NOCOUNT

·         It restricts the ‘T-SQL’ statement or ‘stored procedure’ to show the message which shows that how many rows is affected after executing stored procedure/T-SQL statement.

·         This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.

Why (Advantages)

                  

'SET NOCOUNT ON' turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed in stored procedure. It is not good practice to send the information regarding number of rows affected to client because it will increase network traffic. It is handy while you are running single Query. It increases overall performance of application.

HOW (Implementation)

               

                 In this query, I am selecting the details of all the tables available in adventureworks database. 

I have putted SET NOCOUNT ON. So it will not return the affected rows as message.

SET NOCOUNT ON

GO

SELECT * FROM sysobjects WHERE xtype='U' ORDER BY name ASC

GO

When SET NOCOUNT ON

          

    

            SET NOCOUNT OFF

            GO

            SELECT * FROM sysobjects WHERE xtype='U' ORDER BY name ASC

            GO

       



When SET NOCOUNT OFF

 If you still want number of rows affected, you can use @@ROWCOUNT.The @@ROWCOUNT updated even if SET NOCOUNT is ON.

SET NOCOUNT ON

GO

SELECT * FROM sysobjects WHERE xtype='U' ORDER BY name ASC

SELECT @@ROWCOUNT

GO 



@@ROWCOUNT

 Stored procedure creation template from management studio now by default putting  ‘SET NOCOUNT ON’  from SQL SERVER 2005 onwards.

 Note: This article is purly my view.