SA0017 : SET NOCOUNT ON option in stored procedures and triggers

Introduction

Set NOCOUNT to ON to optimize performance in SQL Server triggers and stored procedures.

Description

When executing triggers and stored procedures in SQL Server, avoiding unnecessary network traffic is crucial for performance. The SET NOCOUNT ON directive prevents the server from sending messages to the client after each statement, like ‘(n row(s) affected)’, which can unnecessarily consume network bandwidth.

Example of a trigger without SET NOCOUNT ON :

CREATE TRIGGER trgExample
ON TableName
FOR INSERT
AS
BEGIN
    SELECT 'Trigger executed';
END

In the example above, SQL Server sends a message for every statement executed, which can become a bottleneck if there are many operations. By adding SET NOCOUNT ON , these messages are suppressed, optimizing the execution.

Significant reduction in network traffic improves performance, especially for large-scale operations.
Enhanced efficiency for procedures and triggers that involve multiple statements.

How to fix

To optimize performance in SQL Server triggers and stored procedures, the SET NOCOUNT ON option should be implemented to minimize unnecessary network traffic.

Follow these steps to address the issue:

1.Open the trigger or stored procedure you want to optimize in SQL Server Management Studio (SSMS).

2.Locate the beginning of the trigger or stored procedure code block, usually after the AS keyword.

3.Insert SET NOCOUNT ON; at the start of the code block. This directive will suppress the “rows affected” messages, reducing network traffic.

4.Ensure that the rest of the trigger or stored procedure logic remains unchanged, except for the addition of SET NOCOUNT ON; .

5.Save the changes to apply the optimization.

Optimized trigger example with SET NOCOUNT ON :

CREATE TRIGGER trgExample
ON TableName
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 'Trigger executed';
END

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.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE HumacnResources.uspGetAllEmployees
AS
SET XACT_ABORT ON;
-- SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

SELECT LastName,
       FirstName,
       JobTitle,
       Department
FROM HumanResources.vEmployeeDepartment;

Example Test SQL with Automatic Fix

CREATE PROCEDURE HumacnResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
-- SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

SELECT LastName,
       FirstName,
       JobTitle,
       Department
FROM HumanResources.vEmployeeDepartment;

Analysis Results

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

Analysis Rules