How to control the the display of the number of rows affected with MSSQL

  • SQL Server always display the number of rows affected by a query or an update.
  • SQL Server use the rowcount to control the display of number of rows affected.
  • Most of SQL Server's operations are on very small datasets, especially with screen operations, where each user only sees or modifies only 1 row at a time. Passing the number of rows affected significantly add overhead to the network and reduce scalability.
  • You can control the rowcount display with: set nocount on.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. go
  3. SELECT counter,emp_short,trans_date,amount
  4. FROM trans01
  5. WHERE counter < 100;go
counter     emp_short  trans_date              amount
----------- ---------- ----------------------- ---------------------
1           000010     2007-05-01 00:00:00     345.00
11          000010     2007-05-02 00:00:00     175.00
21          000010     2007-05-03 00:00:00     -75.00
31          000010     2007-05-05 00:00:00     -100.00
41          000030     2007-05-15 00:00:00     10000.00
51          000030     2007-05-16 00:00:00     435.00
61          000030     2007-05-20 00:00:00     -125.00
71          000030     2007-05-30 00:00:00     -1100.00
81          000110     2007-04-12 00:00:00     -250.00
91          000110     2007-04-30 00:00:00     -75.00

(10 row(s) affected)

Set nocount

  1. SET NOCOUNT ON;
  2. SELECT counter,emp_short,trans_date,amount
  3. FROM trans01
  4. WHERE counter < 100;
  5. go
counter     emp_short  trans_date              amount
----------- ---------- ----------------------- ---------------------
1           000010     2007-05-01 00:00:00     345.00
11          000010     2007-05-02 00:00:00     175.00
21          000010     2007-05-03 00:00:00     -75.00
31          000010     2007-05-05 00:00:00     -100.00
41          000030     2007-05-15 00:00:00     10000.00
51          000030     2007-05-16 00:00:00     435.00
61          000030     2007-05-20 00:00:00     -125.00
71          000030     2007-05-30 00:00:00     -1100.00
81          000110     2007-04-12 00:00:00     -250.00
91          000110     2007-04-30 00:00:00     -75.00
  • The @@ROWCOUNT function is still updated even when SET NOCOUNT is ON
  • set nocount on will NOT display the number of rows affected.
  • set nocount off WILL display the number of rows affected.