[SQL Server] SET NOCOUNT=ON For Improve Performance in Store Prodedure and Trigger

What is a SET NOCOUNT ON

In SQL Server, SET NOCOUNT ON can improve the performance of stored procedures and triggers (In some scenarios) ,In SQL Server SET NOCOUNT ON is used for

• Stop sending messages like xx rows affected 
  Back to the client or application after executing SQL statements.
• This reduces the overhead involved in sending these messages, especially for procedures or triggers that execute many SQL commands.

From Microsoft Documentation

SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that don't return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Benefits of Using SET NOCOUNT ON

• Reduces Network Communication Overhead
  But it reduces capabilities for debugging or logging
• Improves Execution Speed, For Stored Procedures/Triggers with many INSERT, UPDATE, or DELETE commands, turning off these messages improves execution speed.
• Simplifies Client Processing

Example Usage

• For Stored Procedure

CREATE PROCEDURE SampleProcedure
AS
BEGIN
    SET NOCOUNT ON;

    -- SQL Statements
    INSERT INTO TableA (Column1) VALUES ('Value1');
    UPDATE TableB SET Column2 = 'Value2' WHERE Column3 = 'Value3';

    SET NOCOUNT OFF; -- Optional, you don’t need to turn it off explicitly
END

• For Triggers

CREATE TRIGGER trgExample
ON dbo.ExampleTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Your trigger logic here
    INSERT INTO AuditLog (Action, Timestamp)
    VALUES ('Data Modified', GETDATE());

    -- No need to set NOCOUNT back to OFF; it is scoped to the trigger
END

SET NOCOUNT and @@ROWCOUNT function

You can use the @@ROWCOUNT function to get the number of affected rows in SQL Server

Reference

• https://learn.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql
• https://learn.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql