In SQL Server, the SET NOCOUNT ON statement is used to prevent the message that shows the count of the number of rows affected by a T-SQL statement from being returned to the client. This can help improve performance by reducing network traffic when executing a batch of SQL statements, especially in stored procedures and functions where those messages may not be needed.
How to Use SET NOCOUNT ON
To use SET NOCOUNT ON, you simply include it at the beginning of your T-SQL script or within a stored procedure, as shown in the following example:
-- Start the script
SET NOCOUNT ON;
-- Example SQL statements that modify data
INSERT INTO Employees (Name, Position) VALUES ('John Doe', 'Manager');
UPDATE Employees SET Position = 'Senior Manager' WHERE Name = 'John Doe';
DELETE FROM Employees WHERE Name = 'Jane Doe';
-- Optionally, you can set NOCOUNT back to OFF if needed
SET NOCOUNT OFF; -- This is typically not necessary unless you want to see the counts again.
Explanation
1. Before Executing Statements: Insert the SET NOCOUNT ON; statement before any data manipulation commands. This eliminates the row count messages that would normally be returned after each statement.
2. When to Use: You generally want to use SET NOCOUNT ON in stored procedures or batch scripts where you have multiple statements and you're only interested in the result set, not in the row count messages. This is particularly beneficial for performance when many rows are affected and reduces message overload on the client side.
3. Effect: While SET NOCOUNT ON suppresses the message (e.g., "X row(s) affected"), it does not affect the operation of your SQL commands—insertions, updates, or deletions will still occur as intended.
4. Turning it Off: You can turn row counting back on by using SET NOCOUNT OFF, but it's typically not necessary to do so unless you're entering a different part of code where you need to see row counts again.
By using SET NOCOUNT ON, you streamline the communication between SQL Server and your application, making your database interactions more efficient, particularly in high-volume operations.