SA0017 : SET NOCOUNT ON option in stored procedures and triggers

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:

SQL
Copy
1CREATE TRIGGER trgExample
2ON TableName
3FOR INSERT
4AS
5BEGIN
6    SELECT 'Trigger executed';
7END

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:

SQL
Copy
1CREATE TRIGGER trgExample
2ON TableName
3FOR INSERT
4AS
5BEGIN
6    SET NOCOUNT ON;
7    SELECT 'Trigger executed';
8END

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
Copy
 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
Copy
 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

Analysis Rules