Everything You Ever Wanted to Know About SET NOCOUNT

SET NOCOUNT has to be one of the most used T-SQL statements. I can’t think of a single production script or procedure in which I didn’t use this SET statement. And I have to stretch to come up with a case when I would specifically not use it. The only time I don’t use NOCOUNT is when writing off-the-cuff test scripts. I decided to dig into it a bit and see what it really does behind the scenes.

SET NOCOUNT

From BOL:
“Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.”

I learned that the first statement isn’t as clear as it could be. I think it is technically true but easy to misunderstand. It appears to say that it stops the count from being returned in a result set. This is not true. The result still returns the count for statements that return data to the client. However, this count is marked as not to be used.

The second account is a bit misleading. It does stop the DONE_IN_PROC messages (tokens, actually, which I will explain later) from being sent. The reduction in network traffic is very case specific. Now it is always better to have less network traffic than more but I can think of only a few cases where a significant difference would be noticed.

Examining this requires a bit of understanding of Tabular Data Streams (TDS). I’m not interested in writing a series of columns about TDS at the moment so I’m going to keep this intro as short as possible. One of the great things about TDS is that we don’t have to know a lot about it ’cause it just works.

Quoting Microsoft on TDS.

“The Tabular Data Stream (TDS) Protocol is an application-level protocol used for the transfer of requests and responses between clients and database server systems. In such systems, the client will typically establish a long-lived connection with the server. Once the connection is established using a transport-level protocol, TDS messages are used to communicate between the client and the server. A database server can also act as the client if needed, in which case a separate TDS connection must be established. Note that the TDS session is directly tied to the transport-level session, meaning that a TDS session is established when the transport-level connection is established and the server receives a request to establish a TDS connection. It persists until the transport-level connection is terminated (for example, when a TCP socket is closed). In addition, TDS does not make any assumption about the transport protocol used, but it does assume the transport protocol supports reliable, in-order delivery of the data.”

In short, when you get data back from SQL Server it is in the format defined by the TDS protocol. If we understand a little bit about TDS we can capture the packets on the wire and examine them. For me seeing examples was the best way to understand what is going on and I demonstrate some of these.

A few TDS definitions first

These definitions will make much more sense when I apply it to an example.

Response: “A TDS message sent by a server to a client related to a previously issued request.”

Result Set: “A set of data streams representing the result of a query. A result set starts with a COLMETADATA token and ends with a DONE, DONEPROC, or DONEINPROC token.”

From looking at the traces I learned that a single response will contain multiple result sets. This surprised me. I expected each result to be encapsulated with its own protocol header. The entire TDS packet had a header but each result set did not. Multiple result sets (not MARS) returned from the execution of a single batch were simply stacked on after another sequentially in the response. A COLMETADATA token in the result stream indicated the beginning of another result set.

Responses come in two forms: tokenless and token streams. I’m not going to talk about tokenless streams because they aren’t applicable for this scenario and I didn’t take the time to learn about them. A token is “a single byte identifier used to describe the data that follows it…” A token stream uses tokens to delineate different parts of the TDS packet. The effect is a token with some data following that is applicable to that token. Then another token with data following it that is applicable to that specific token, etc. This will be easy to see in the example.

My NOCOUNT Test Case

To get started I captured very simple packets but to get a better understanding of what is happening I decided to execute a more complex hierarch of statements. In my test case I created a batch with many dependent child procedures so that the thread of execution would have to go many levels deep into the stored procedure and statement execution. I wanted to see how the complexity of the batch affected the results returned.

IF EXISTS
(
SELECT NULL FROM sys.objects 
WHERE object_id = object_id('Child1','P')
)
DROP PROCEDURE Child1
GO

CREATE PROCEDURE Child1
AS
--SET NOCOUNT ON
SELECT CAST('Miami' as varchar(10)) [City],AddressLine1
FROM Person.Address WHERE City = 'Miami'
EXEC Child2
SELECT CAST('Salem' as varchar(10)) [City],AddressLine1
FROM Person.Address WHERE City = 'Salem'
RETURN 765432
GO

IF EXISTS 
(
SELECT NULL FROM sys.objects 
WHERE object_id = object_id('Child2','P')
)
DROP PROCEDURE Child2
GO

CREATE PROCEDURE Child2
AS
EXEC Child3A
SELECT CAST('Houston' as varchar(10)) [City], AddressLine1
FROM Person.Address WHERE City = 'Houston'
EXEC Child3B
GO

IF EXISTS 
(
SELECT NULL FROM sys.objects 
WHERE object_id = object_id('Child3A','P')
)
DROP PROCEDURE Child3A
GO

CREATE PROCEDURE Child3A
AS
EXEC Child4
SELECT CAST('Dallas' as varchar(10)) [City],AddressLine1
FROM Person.Address WHERE City = 'Dallas'
GO

IF EXISTS 
(
SELECT NULL FROM sys.objects 
WHERE object_id = object_id('Child3B','P')
)
DROP PROCEDURE Child3B
GO

CREATE PROCEDURE Child3B
AS
SELECT CAST('New York' as varchar(10)) [City],AddressLine1
FROM Person.Address FROM City = 'New York'
GO

IF EXISTS 
(
SELECT NULL FROM sys.objects 
WHERE object_id = object_id('Child4','P')
)
DROP PROCEDURE Child4
GO

CREATE PROCEDURE Child4
AS
SELECT CAST('Chicago' as varchar(10)) [City],AddressLine1
FROM Person.Address WHERE City = 'Chicago'
CREATE TABLE #Test (AddressLine1 nvarchar(60))
INSERT INTO #Test (AddressLine1) SELECT AddressLine1
FROM Person.Address WHERE City = 'Seattle'
GO

This is fairly complicated for a blog example so I wrote out in plain English the order of execution. As you can see in each SELECT statement in each procedure I am returning a virtual column that contains the city I’m constraining the search by. This makes it much easier to see the result set in a data capture. By looking at the batch and the stored procedures being called we can imagine the thread of execution flowing through it. We should see this order of execution.

Starting batch
Set NOCOUNT
Select Tampa in batch
Exec Procedure Child1
Select Miami in Child 1
Exec Procedure Child 2
Exec Procedure Child 3A
Exec Procedure Child 4
Select Chicago in Child 4
Create Table in Child 4
Insert into Table in Child4
Return to Child 3a
Select Dallas in 3a
Return to Child2
Select Houston Child2
Exec Procedure Child 3B
Select New York in Child 3b
Return to Child2
Return to Child1
Select Salem in Child1
Return to batch
Exit batch

The pictures I show are from the Packet Bytes Pane of a Wireshark packet capture. Each picture has three components. On the left is the offset number of the bytes in that row. Line 10 is actually 0x10 which converts to decimal 16. There are 16 bytes represented on each row. The middle section contains the actual bytes in the packet. The right section contains the ASCII representation of the bytes if applicable. It isn’t a very robust translation but it is good enough for us to easily see where we are in the packet and is the reason I included the city name in my results. My sample selections include the city name so that we can use the right section to help orient ourselves in the packet.

The SET NOCOUNT Results

First I want to look at some of the TDS data with NOCOUNT turned OFF.
Packet1A

Using the right section of the wireshark capture and the knowledge that each statement completion has a DONE-type token we can pick our way through the TDS packet. I’ve highlighted each type of token so it is easier to pick out the stream.
DONE, DONEINPROC and DONEPROC tokens are in yellow. These have the hex values of 0xfd, 0xfe and 0xff respectively and are typically 13 bytes long.
COLMETADATA tokens are in green
ROW tokens are in light red.

Within the DONE-type tokens are fields of information.
Status – pink (2 bytes)
CurCmd – blue (2 bytes but I won’t be talking about this at all)
DoneRowCount – Orange (4 or 8 bytes depending on the version of TDS. In our example it is 8.)

At offset 0000 we see the very first DONE token. This is from the SET NOCOUNT statement. The follow 12 bytes are not of interest right now. However, in the same row we see a COLMETADATA token. This indicates the beginning of a result set. In the offset row 0040 we see a ROW token (d1 highlighted in red). By looking at the right pane we see that this is a row from the Tampa SELECT statement in the beginning of the batch. And finally in offset row 0070 there is a DONE token indicating the end of the Tampa select statement results.

The content of this done token data stream are interesting and directly applicable to the NOCOUNT topic. The status field highlighted in pink gives us more information about the result set. It uses a bitwise OR to combine various hex values into these two bytes. Two of the possible statuses are:
0x1: DONE_MORE – This means that this is not the final DONE in the response and that more data streams will follow
0x10: DONE_COUNT – This means that the count given to us in the DoneRowCount field is valid. The DoneRowCount stream is in orange. Looking at the DoneRowCount field we see that the value is 0x01. This converts to 1 in decimal. This is the number of rows affected/returned by the Tampa SELECT.

The short explanation is that 0x11 or 0x10 in the pink status indicates that we can take the 0x1 count in the orange section as value. This changes when SET NOCOUNT is on as I will show later in a data capture.

After the end of the DoneRowCount section of the DONE token we see another COLMETADATA token. This indicates the beginning of another result set. By looking at the right pane in the data capture we can see that it is from the Miami SELECT. As an interesting aside there is nothing in the response that indicates that the Miami SELECT is in a different procedure.

Now I will skip down to another section of the response, the multiple statements in procedure Child4.
Packet2A

By looking at the right hand pane in the capture we can see that the DONEINPROC token (0xff highlighted in yellow) on offset line 0510 is for the Chicago SELECT. In our previous example we saw a DONE token (0xfd). On this occasion the statement completed is in a procedure and not the root of the batch so the DONE token is of a different flavor. Once again the pink section shows the status of 0x11 which is a bitwise OR of 0x1 and 0x10. Recall that these mean that there are more results to follow this one and that the row count in the orange section (0xb) is valid. 0xb converts to 11 decimal, which is an accurate count of the number of rows returned.

There is a second 0xff token on this offset line. This is for the creation of a temp table in procedure Child4. The 0xff token in offset line 0520 is the insert of records into this temp table. The status of this token 0x10 indicates that the count is valid and the count is 0x8d. 0x8d is 141 and this is the number of rows inserted into the table. There is one more 0xff token before the next COLMETADATA token on offset line 0540. This is the completion of the EXEC statements in Child3A that calls procedure Child4.

Now the capture of the end of the response:
Packet3A
The 0xff token on offset 1a70 is for the Salem SELECT. The status of 0x11 again indicates the count of 0x65 (101 decimal) is valid and that more DONE tokens are expected. Highlighted in green on this capture is the token 0x79. This is a RETURNSTATUS token. It has a token stream (brown) indicating what the return value is. Notice that in my last procedure, Child1, I have a RETURN statement with the value of 765432. If we translate the hex of 0xf8, 0xad, 0x0b it converts to 765432. The bytes are stored in BIG ENDIAN order so we’d type 0xbadf8 into our conversion calculation.

Now, here is the final done token, 0xfe, DONEPROC. This is the final result set in the response. We know this because the status is value 0x0 instead of 0x1.

Before continuing I think it is important to note some interesting insights from this packet. The first statement in the batch is SET NOCOUNT OFF. The second statement in the batch is a query for addresses in Tampa. The third query in the batch is actually inside of a stored procedure called from the batch. What is interesting to note is that there is no separation between the results packets delineating where in the batch the specific clause is executed. In other words we cannot tell from the response which procedure in our batch produce which results. We see that the results from each statement are simply stacked one after the other. This also means that our app can’t tell either. The implementation is invisible to the client.

Comparing this to a batch where NOCOUNT is ON
Packet1B
This is very similar to the capture with NOCOUNT OFF. You will notice though for the DONE token in offset 0070 (0xfd in yellow) the status (in pink) is 0x01 instead of 0x11. Recall that 0x1 means that there are more result sets to follow and that 0x10 means that we can trust the count returned by the DONE data stream. 0x01 instructs the client to NOT trust the count returned in the DoneRowCount field. The count (in orange) is actually returned with the result set and is the same as when NO COUNT was OFF. In this case setting NOCOUNT ON doesn’t actually save any network traffic since the count is included in the result set. It is a meager bit in the status field that tells us if we can use the count or not.

Now to the middle of the response aligning with the multiple statements in procedure Child4 I wrote about earlier:
Packet2B

Compared to the previous capture we can see that several DONEINPROC tokens are missing. This works in accordance with the documentation in that “SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.” The statements that return actual data still get a DONEINPROC token otherwise the tokens are eliminated from the response. Note also that the status (pink) of the DONEINPROC token at offset 0510 is 0x01 indicated there are more result sets to come and that we can’t trust the count returned in the DoneRowCount stream (orange) even though the DoneRowCount does contain an accurate count of the rows returned.

And the capture at the end of the response
Packet3B
No significant difference from the initial capture. The status of the DONEINPROC token that starts on line offset 1a30 is 0x01 which is to be expected when NOCOUNT is disabled.

I created this chart to help compare the two responses including the DONE tokens with the Status and DoneRowCount fields.

Statement Token (with nocount off) Token (with nocount on)
Set NoCount fd 01 00 ba 00 00 fd 01 00 b9 00 00
Select Tampa in batch fd 11 00 c1 00 01 fd 01 00 c1 00 01
Enter Procedure Child1 No token No token
Select Miami in Child 1 ff 11 00 c1 00 0a ff 01 00 c1 00 0a
Enter Procedure Child 2 No token No token
Enter Procedure Child 3A No token No token
Enter Procedure Child 4 No token No token
Select Chicago in Child 4 ff 11 00 c1 00 0b ff 01 00 c1 00 0b
Create Table in Child 4 ff 01 00 c6 00 00 None
Insert into Table in Child4 ff 11 00 c3 00 8d None
Back to Child 3a ff 01 00 e0 00 00 None
Select Dallas in 3a ff 11 00 c1 00 07 ff 01 00 c1 00 07
Back to Child2 ff 01 00 e0 00 00 None
Select Houston Child2 ff 11 00 c1 00 05 ff 01 00 c1 00 05
Enter Procedure Child 3B No token None
Select New York in Child 3b ff 11 00 c1 00 02 ff 01 00 c1 00 02
Back to Child2 ff 01 00 e0 00 00 None
Back to Child1 ff 01 00 e0 00 00 None
Select Salem in Child1 ff 11 00 c1 00 65 ff 01 00 c1 00 65
Back to batch 79 e6 1d 00 00 79 f8 ad 0b
End batch fe 00 00 e0 00 00 fe 00 00 e0 00 00

The documentation claims that setting NOCOUNT on can have significant network bandwidth savings. As we can see this is on a case-by-case basis determined by the number of non-data returning statements being executed relative to the amount of results returned. A WHILE loop that executes 10,000 times in a procedure that only returns one row will see a great reduction in traffic relative to the size of the entire result set. But a few non-data returning statements in a batch that returns thousands and thousands of rows will see practically zero percentage change in bandwidth.

How SET NOCOUNT Interacts With Your Client

When developing a client the count in the DoneRowCount is what is given as the number of rows affected/returned. If there are multiple statements returning multiple results and multiple DoneRowCount fields these numbers are totaled up as a single value by the client. You can affect this count returned by changing NOCOUNT in the middle of a procedure. I created this procedure to test this.

CREATE PROCEDURE dbo.CountTest
AS
SET NOCOUNT ON
SELECT CAST('Miami' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Miami'
SET NOCOUNT OFF
SELECT CAST('Dallas' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Dallas'
SET NOCOUNT ON
SELECT CAST('Houston' as varchar(10)) [City], AddressLine1 FROM Person.Address WHERE City = 'Houston'
SET NOCOUNT OFF
SELECT CAST('New York' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'New York'
SET NOCOUNT ON
SELECT CAST('Salem' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Salem'
SET NOCOUNT OFF
SELECT CAST('Chicago' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Chicago'
GO

Calling this procedure from a client should result in the rows selected count to be the sum of the rows from the Dallas, New York, and Chicago queries (the count is 20). I wrote a little .Net app to show this.

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Connection.StatisticsEnabled = true;

command.CommandText = "CountTest";
command.CommandType = System.Data.CommandType.StoredProcedure;

int affected;
Int64 rows;
connection.Open();
affected = command.ExecuteNonQuery();
System.Collections.IDictionary stats = command.Connection.RetrieveStatistics();
rows = (Int64)stats["SelectRows"];

Using the stats from the connection I was able to see the number of selected rows. I put a watch on rows and the value returned was 20.
rowsreturned

Most of the time, in a multistatement procedure, the number of rows affected is not useful for any business purposes. However if the rows affected by some combination of statements does have some business use we can use NOCOUNT selectively to influence the value returned to the client.

5 thoughts on “Everything You Ever Wanted to Know About SET NOCOUNT

  1. This post is awesome. I’ve wanted to do some research on NOCOUNT ON and how useful it really is and you spell it out perfectly. Thanks for putting the time in on this.

Leave a Reply

Your email address will not be published. Required fields are marked *