CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sleiman Jneidi
Sleiman Jneidi

Reputation: 23339

SQL Server keeps on counting rows

I've disabled row counting using SET NOCOUNT ON but it seems that sqlserver is still counting rows.

USE Northwind
SET  NOCOUNT ON;
SELECT * FROM Products p
SELECT @@ROWCOUNT AS 'RowCount';

the query returns 77 for row count, why?

Upvotes: 3

Views: 115

Answers (2)

DiverseAndRemote.com
DiverseAndRemote.com

Reputation: 19888

SET NOCOUNT ON;

prevents the rowcount from being returned when you execute your stored procedure. It has no affect on @@rowcount.

see http://msdn.microsoft.com/en-us/library/ms189837.aspx for specific info

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

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."

Upvotes: 6

Guffa
Guffa

Reputation: 700592

The nocount setting doesn't keep the database from counting rows, it keeps it from returning a result set for update and insert queries.

Example:

create procedure test1
as
insert into Products (ProductName) values ('Fork')
select scope_identity() as ProductId

Calling that procedure would return two result sets, the first one is empty and has the information that the number of affected rows is one, and the second contains the record with the product id.

vs.:

create procedure test1
as
set nocount on
insert into Products (ProductName) values ('Fork')
select scope_identity() as ProductId

Calling that procedure will only return one result set, containing the product id.

Upvotes: 1

Related Questions