SET NOCOUNT

Continuing on the series on SET statements, this time it is SET NOCOUNT.

Let us run the following query.

USE AdventureWorks2012
GO

SELECT * FROM Sales.SalesOrderHeader

Of course you will see the output and if you look at messages tab you will see,

(31465 row(s) affected)

So when an query is executed DONE_IN_PROC messages is sent to the client. A DONEINPROC token is sent for each executed SQL statement within a stored procedure.

Token Stream-Specific Rules:

TokenType        =   BYTE
Status           =   USHORT
CurCmd           =   USHORT
DoneRowCount     =   LONG / ULONGLONG;  

Details are here for token DONE_IN_PROC. 

When SET NOCUNT is set to ON ( default value is OFF), this token will not be sent. Since this token is sent for each and every statement, by setting this to ON, you can save lot of network traffic. 

SET NOCOUNT ON

USE AdventureWorks2012
GO

SELECT * FROM Sales.SalesOrderHeader

When this is executed at the messages tab you will see following results.

Command(s) completed successfully.

However, @@ROWCOUNT will not be effected with SET NOCOUNT ON setting.

Previous articles of this series,

SET IMPLICIT_TRANSACTIONS ON