Microsoft SQL Server ResourcesImprove SQL Server Stored Procedure Performance by using SET NOCOUNT ON

Provided by: FMS Development Team

In Microsoft SQL Server, when executing a Transact-SQL statement, the number of "rows affected" is returned by default.

For example, execute the following in SQL Analyzer:

USE Pubs
BEGIN
  UPDATE authors
  SET Contract = 1
  WHERE State = 'KS'
END

when this query is executed, the messages pane displays:

(1 row(s) affected)

Because additional processing time is needed to ascertain the number of rows affected, turning off this feature reduces the time needed to process the stored procedure. This is accomplished by using the SET NOCOUNT ON command.

Add it to the start of the example and when executed, notice that the rows affected message is suppressed.

USE Pubs
BEGIN
SET NOCOUNT ON
  UPDATE authors
  SET Contract = 1
  WHERE State = 'KS'
END

We agree the terminology is odd that to turn off the feature, one needs turn ON the NOCOUNT command. The reverse is also true. To reactivate the rows affected, use SET NOCOUNT OFF.

Strategic Overview

SQL Server Express

SQL Azure Cloud

What it Means to Information Workers

Link Microsoft Access to SQL Azure Databases

Deploy MS Access DBs linked to SQL Azure

Eliminate SQL Azure Security Holes

Monitor SQL Server Usage and DTU Limits on Azure

Convet Azure SQL Server to Elastic Pools

SQL Server Tips

Database Users and Permissions

Database Backup and Restore on Network

AM/PM Date Time Format

Get Date Portion

Set NOCOUNT ON

Running Total

Videos

Migrate Your Data Tier to SQL Server: Strategies for Survival

Microsoft Access Database and Migration Challenges

Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Services

Microsoft SQL Server Consulting Services

Visual Studio .NET programmers for web development

General Microsoft Access consulting services

Additional Resources

Microsoft Access Help

Microsoft Access Developer Help Center

MS Access Developer Programming

More Access and SQL Tips

Technical Papers

Microsoft Access Tools

Connect with Us

 

Free Product Catalog from FMS