SQL Server - Session settings in procedure

In SQL Server, SET NOCOUNT ON; is a command used to stop the message that shows the number of rows affected by a Transact-SQL statement from being returned as part of the result set.

When you run a query like UPDATE or INSERT, SQL Server usually sends a message back saying something like (1 row(s) affected). While helpful in a manual query window, this can cause issues in application code and stored procedures.

Why use it?

• Performance Boost: For procedures with many loops or multiple statements, suppressing these messages reduces "network traffic" between the server and the client.

• Preventing App Errors: Some application frameworks (like older versions of ADO.NET) can misinterpret the "rows affected" message as an actual result set, which can cause the application to crash or ignore the real data being returned.

• Cleaner Output: It keeps the "Messages" tab in SQL Server Management Studio (SSMS) clean, focusing only on errors or intentional print statements.

---

Comparison: ON vs. OFF

Feature	SET NOCOUNT ON;	SET NOCOUNT OFF; (Default)
Message Sent	No "rows affected" message is sent.	Sends "(n row(s) affected)" to the client.
@@ROWCOUNT	Still updated and usable in logic.	Updated and usable in logic.
Overhead	Lower (reduced network packets).	Slightly higher.

Example Usage

You typically place this at the very beginning of your stored procedure, right after the AS keyword:

CREATE PROCEDURE UpdateEmployeeStatus
    @EmployeeID INT
AS
BEGIN
    -- This stops the "1 row affected" message
    SET NOCOUNT ON;

    UPDATE Employees 
    SET IsActive = 1 
    WHERE ID = @EmployeeID;
    
    -- You can still use @@ROWCOUNT for logic!
    IF @@ROWCOUNT = 0
    PRINT 'No employee was found with that ID.';
END

A Common Misconception

A common worry is that SET NOCOUNT ON will break logic that relies on @@ROWCOUNT. This is not true. The @@ROWCOUNT function is updated regardless of the NOCOUNT setting, so your internal logic (like checking if an update succeeded) will still work perfectly.

---

Would you like me to explain other common session settings, such as XACT_ABORT or ANSI_NULLS?

 

Yes.

 

In addition to SET NOCOUNT ON, there are two other session settings that are considered "best practice" for almost every SQL Server stored procedure.

Using these three together ensures your procedures are performant, handle errors correctly, and follow standard SQL behavior.

---

1. SET XACT_ABORT ON

This is arguably the most important setting for data integrity.

• What it does: When set to ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

• Why use it: By default (OFF), SQL Server might continue executing the rest of the procedure even if one statement fails. This can lead to "partial completions" where half your data is updated and the other half isn't.

• Recommendation: Always use this when using BEGIN TRANSACTION to ensure "all or nothing" execution.

2. SET ANSI_NULLS ON

This controls how the database handles NULL values during comparisons.

• What it does: When ON, any comparison against a NULL value using = or <> results in UNKNOWN. You must use IS NULL or IS NOT NULL.

• Why use it: This is the ISO standard. If set to OFF, NULL = NULL would evaluate to true, which can lead to unpredictable results and logic bugs.

• Note: In future versions of SQL Server, this will be permanently set to ON, and the ability to turn it OFF will be removed.

---

The "Standard" Procedure Template

Most professional SQL developers start their scripts with this standard block to ensure a clean, predictable environment:

CREATE PROCEDURE dbo.MyProducure
AS
BEGIN
    -- 1. Suppress 'rows affected' messages
    SET NOCOUNT ON;
    
    -- 2. Ensure total rollback on error
    SET XACT_ABORT ON;

    -- 3. Use standard NULL comparison behavior
    -- (Usually set at the database level, but good to keep in mind)
    
    BEGIN TRY
        BEGIN TRANSACTION;
            
            -- Your logic here
            
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        -- Handle error here
        THROW; 
    END CATCH
END

Summary Table

Setting	Recommended Value	Primary Benefit
SET NOCOUNT	ON	Improves performance and prevents app interface errors.
SET XACT_ABORT	ON	Guarantees data integrity by rolling back on any error.
SET ANSI_NULLS	ON	Ensures standard, predictable behavior for NULL values.