How It Works: SqlDataReader::RecordsAffected and Why it Returns -1

[mssql-support]

First published on MSDN on Jun 08, 2010

I encountered another interesting research issue to share with you.  The documentation states RecordsAffected are for INSERT, UPDATE and DELETE but it might fool you.

------------------------------------------------------------------------------------

Sent: Monday, June 07, 2010 9:06 PM
Subject: "set nocount off"

I am trying to figure out the logic behing “set nocount”.  I want to get the row count when the procedure is executed so I can display a progress bar based on the returned rows (using SqlDataAdapter to read the data – so I need to know the count in advance).  Since I need a temp table, I set “nocount on” at first, then I set it to “set nocount off” before I returned the record set.  I aways get “-1” in RowsAffected.  I  also tried using “Print” or “RaiseError” with  InfoMessage, but the event is never raised.   Any ideas on how to accomplish this task? Or why I get “-1” (i.e. what are the conditions for “-1” when “set nocount off” is in effect?).  Thanks.

------------------------------------------------------------------------------------

From: Robert Dorr
Subject: RE: "set nocount off"

The count is always returned at the end of the result set.   So you idea about a progress bar based on count won’t work with nocount logic.   If you want this you need to do a select count(…..) query, read that result set and then run the query to returns the actual rows.   This generally means you have to run the query 2 times so I am not sure this is really what you want.

Most of the time I have seen folks just build a progress bar with a count of say 1000 and as they fetch rows they increment the progress counter and let it wrap around to show they are doing work but display a row count (TEXT) to show that rows are being fetched.

------------------------------------------------------------------------------------

Sent: Tuesday, June 08, 2010 9:24 AM
Subject: RE: "set nocount off"

The sample below gives me an accurate count of the “rowcount/RecordsAffected” up-front.  It works in one case but not in others, so I am trying to figure out what is affecting the “RecordsAffected” property – so I can explain why in some cases I am getting “-1” and in others, I am getting an accurate count.  Perhaps,  this is a questions for the ADO.NET alias;  I just want to make sure that I understand the server behavior (for example: what happens if use multiple times “set nocount on/off” in a single proc).   Thanks for replying.

SqlCommand cmd = new SqlCommand("sp_Test", sqlConnection);

SqlDataReader dr = cmd.ExecuteReader();

int i = dr.RecordsAffected;

if (i > -1) ShowProgressBar...

create procedure sp_Test

as

set nocount off

create table #t (ID int)

declare @i int

set @i = 1000

while @i > 0

begin

insert #t select @i

set @i = @i - 1

end

select * from #t

drop table #t

------------------------------------------------------------------------------------
From: Robert Dorr
Sent: Tuesday, June 08, 2010 10:50 AM
Subject: RE: "set nocount off"

I am suspicious that the answer is that RecordsAffected are for INSERT, UPDATE and DELETE and not SELECT but I need to do a bit more digging.

RecordsAffected

Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement. (Overrides DbDataReader.RecordsAffected .)

------------------------------------------------------------------------------------

Sent: Tuesday, June 08, 2010 9:58 AM
Subject: RE: "set nocount off"

In calss ADO, there used to be sql Errors collection on the connection object – where I can retrieve “print” messages.  I ADO.NET, they created an InfoMessage event but it’s never fired in my tests – I was hoping to simply “print” the @@rowcount and retrieve it via the event…

For the RecordsAffected, it’s definitely affected by the “select”  - it’s just that there is no doc on how it’s affected – for example, I use ‘set nocount on” to avoid messages fro previous statements, and then set it to “set nocount off’ for the stmt in question, then the RecordsAffected becomes “-1” (weather it’s before or after I retrieve the recordset)….it’s a very nice feature but lack of docs is making it unusable. J  Thanks for looking into this.

------------------------------------------------------------------------------------

From: Robert Dorr
Sent: Tuesday, June 08, 2010 11:15 AM
Subject: RE: "set nocount off"

I can repro the behavior with a simple “select 1” command to return -1 for the RowsAffected.

I followed the SqlCommand::ExecuteReader code down to the actual TDS invocation to see what it is doing.

When the SqlDataReader is created the Rows Affected is set to -1.

SqlDataReader (…)

{

this. _recordsAffected = -1;

…

Then I followed the logic into the TDS parser into the ProcessDone logic (the set nocount controls the done and done_in_proc token generations).

It is similar to what I thought it was.   The reader does not fetch all rows you have to read the rows to get to the done for the select statement so the data reader does not appear to be keeping track of this because you already had to fetch all the rows anyway.

Then I tried the following and I get back 10,000 for the rows affected.  Correct – the batch affected (INSERTED 10000 rows)

SqlCommand cmd = new SqlCommand("declare @i int; create table #tmp (iID int); set @i=0; while(@i < 100000) begin insert into #tmp values(@i) set @i = @i +1 end; select * from #tmp", sqlConn);

SqlDataReader dr = cmd.ExecuteReader();

int i = dr.RecordsAffected;

So I changed to the following TOP and I still get 10,000 – Expected the select(s) don’t set the RecordsAffected

SqlCommand cmd = new SqlCommand("declare @i int; create table #tmp (iID int); set @i=0; while(@i < 100000) begin insert into #tmp values(@i) set @i = @i +1 end; select TOP 10 * from #tmp", sqlConn);

I then used variants of set nocount and you can see you can impact the number of rows affected returned by where you place the set statement.   In this example I insert (affect 20000 rows) but if you set the nocount on around the first 10000 the RecordsAffected returns 10000 instead of 20000.

SqlCommand cmd = new SqlCommand("set nocount on; declare @i int; create table #tmp (iID int); set @i=0; while(@i < 100000) begin insert into #tmp values(@i) set @i = @i +1 end; set @i=0; set nocount off; while(@i < 100000) begin insert into #tmp values(@i) set @i = @i +1 end; select TOP 10 * from #tmp", sqlConn);

SqlDataReader dr = cmd.ExecuteReader();

int i = dr.RecordsAffected;

The only reliable way to get the count of a specific select is NOT the RecordsAffected but to count the rows as you read them.

Back to what you are trying to do is determine the number of rows that the select will return to show progress it won’t be RecordsAffected.   This goes back to the age old documentation and patterns I have worked since 1994 that you must process all your results Read and NextResult loops or you can end up with unexpected behaviors.   I have seen customers execute a stored procedure that returns lots of results (NextResult) that take more than one TDS packet.  They only fetch the first result and dispose the command.   It used to be DBLIB and dbexec or ODBC SQLExecuteDirect and then dbcancel/SQLCancel/SQLFreeStmt to dump the reset of the results.  The problem is that this sends the attention/cancel to the SQL Server so if the rest of the procedure/batch has not run yet the execution is cancelled.   So the user thought they ran the entire batch/procedure and only part of it executed.

You mentioned you are using an event to print @@ROWCOUNT but that has the same issue as I described.  The event won’t fire until all the rows from the select have been processed and you progress to the NextResult.   The following code shows that behavior.   If you want the row count before the select in your sample you need to print/select the number of rows before the select and that is generally performance prohibitive as you end up running the query twice.

using System;

using System.Data.SqlClient;

using System.Text;

namespace ConsoleApplication1

{

class Program

{

static void InfoMessageHandler(object sender, SqlInfoMessageEventArgs e)

{

Console.WriteLine(e.ToString());

}

static void Main(string[] args)

{

SqlConnection sqlConn = new SqlConnection(@"Data Source=.SQL2008;Integrated Security=SSPI;");

&nbsp