Click or drag to resize

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.

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.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
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;

Analysis Results

 MessageLineColumn
1SA0017 : SET NOCOUNT ON option in stored procedures and triggers.10
See Also

Other Resources