SA0017 : SET NOCOUNT ON option in stored procedures and triggers

The topic describes the SA0017 analysis rule.

Message

SET NOCOUNT ON option in stored procedures and triggers

Description

This rule scans triggers and stored procedures to ensure they SET NOCOUNT to ON at the beginning.

Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production environments, as this prevents the sending of DONE_IN_PROC messages and suppresses messages like ‘(1 row(s) affected)’ to the client for each statement in a stored procedure.

For stored procedures that contain several statements that do not return much actual data, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

How to fix

Add SET NOCOUNT ON option.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1CREATE PROCEDURE HumacnResources.uspGetAllEmployees
 2AS 
 3SET XACT_ABORT ON;
 4-- SET NOCOUNT ON;
 5SET QUOTED_IDENTIFIER ON;
 6
 7SELECT LastName,
 8       FirstName,
 9       JobTitle,
10       Department
11FROM HumanResources.vEmployeeDepartment;

Example Test SQL with Automatic Fix
SQL
 1CREATE PROCEDURE HumacnResources.uspGetAllEmployees
 2AS 
 3SET NOCOUNT ON; 
 4SET XACT_ABORT ON;
 5-- SET NOCOUNT ON;
 6SET QUOTED_IDENTIFIER ON;
 7
 8SELECT LastName,
 9       FirstName,
10       JobTitle,
11       Department
12FROM HumanResources.vEmployeeDepartment;

Analysis Results
  Message Line Column
1 SA0017 : SET NOCOUNT ON option in stored procedures and triggers. 1 0
See Also

Other Resources