What is the use of set Nocount on in SQL Server?

In SQL Server, the SET NOCOUNT ON statement is used to stop the message that SQL Server sends after each statement indicating the number of rows affected by that statement. When NOCOUNT is set to ON, SQL Server will not return the "x rows affected" message after each query or stored procedure execution. This can be useful in certain scenarios for performance optimization, particularly when executing multiple queries in a batch or when running stored procedures that do not need to return row count information.

Key Uses of SET NOCOUNT ON:

1. Performance Improvement: In environments where many queries or stored procedures are executed in a batch (e.g., in loops or stored procedures), setting NOCOUNT ON can reduce unnecessary network traffic, as SQL Server won't send the "rows affected" message after each query execution. This can be especially helpful when executing large numbers of queries in a single transaction.

2. Cleaner Output: If you don't need the row count information, turning NOCOUNT ON helps to clean up the output. This is useful when you're calling a stored procedure from an application or another process, and you only care about the actual result set, not the row count.

3. Avoiding Row Count Messages in Stored Procedures: When writing stored procedures that don't need to return a row count for each SQL statement, SET NOCOUNT ON prevents these messages from being included in the result.

Example:

SET NOCOUNT ON;  -- Disable the "rows affected" message

-- Your SQL statements here
UPDATE Employees SET Salary = Salary + 1000 WHERE DepartmentID = 2;
INSERT INTO LogTable (Message) VALUES ('Salary updated for Department 2');

SET NOCOUNT OFF;  -- Optional: If you want to turn the behavior back off for the remainder of the session or script

In this example, the updates and insertions are made, but SQL Server won't return a "2 rows affected" message after each statement when NOCOUNT is set to ON.

When to Use SET NOCOUNT ON:

• Stored Procedures: If you're writing a stored procedure and don't need row counts after each SQL statement.
• Performance Optimization: In high-performance applications where minimal network traffic is crucial, especially when multiple queries are executed in batches.
• Cleaner Application Output: When executing queries from applications or reporting tools that don't require the row count information for each SQL operation.

Default Behavior:

By default, SQL Server sends the row count message after every SQL statement. Setting NOCOUNT to ON suppresses this message.

Considerations:

• Setting NOCOUNT ON does not affect the actual results of the query (e.g., the rows returned in a SELECT query or the data modifications). It only suppresses the "x rows affected" message.
• It can be useful in stored procedures that are designed to execute multiple commands where the row count is not necessary for the consumer of the data.

In summary, SET NOCOUNT ON helps to reduce unnecessary network traffic and provides cleaner output, especially when you're working with stored procedures or batch processing queries in SQL Server.