SQL Server keeps on counting rows

sql, sql-server, sql-server-2008

Solution

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

Problem

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?

Original source