SA0017 : SET NOCOUNT ON option in stored procedures and triggers
The topic describes the SA0017 analysis rule.
SET NOCOUNT ON option in stored procedures and triggers
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.
Add SET NOCOUNT ON option.
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
Performance Rules, Bugs
There is no additional info for this rule.
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;
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;
|1||SA0017 : SET NOCOUNT ON option in stored procedures and triggers.||1||0|