← Back to Article List         
Important SET Statements Every SQL Server Developer Must Know

Important SET Statements Every SQL Server Developer Must Know

Published on 18 Sep 2026     24 min read MS SQL
SET Statement

Important SET Statements in SQL Server

SET options normally affect only the current connection/session. If your application opens another connection, it may have different settings.

1. SET NOCOUNT

What it does

Controls whether SQL Server sends the “rows affected” message after every statement.

NOCOUNT OFF

SET NOCOUNT OFF;

UPDATE dbo.Employees
SET Salary = Salary + 1000
WHERE DepartmentId = 10;

Result

(5 rows affected)

NOCOUNT ON

SET NOCOUNT ON;

UPDATE dbo.Employees
SET Salary = Salary + 1000
WHERE DepartmentId = 10;

Result

The update is completed, but this message is not sent:

(5 rows affected)

@@ROWCOUNT still works:

SELECT @@ROWCOUNT AS UpdatedRows;

Result:

UpdatedRows
5

Why use it?

It:

  • Reduces unnecessary network messages.

  • Prevents applications from confusing row-count messages with result sets.

  • Is especially useful in stored procedures and triggers.

Recommended usage

CREATE PROCEDURE dbo.UpdateEmployeeSalary
AS
BEGIN
    SET NOCOUNT ON;

    -- Procedure statements
END;

NOCOUNT ON does not stop rows from being updated and does not disable @@ROWCOUNT.

2. SET XACT_ABORT

What it does

Controls whether SQL Server automatically rolls back the complete transaction when a runtime error occurs.

  • ON: Most runtime errors roll back the complete transaction.

  • OFF: In some cases, only the failed statement is rolled back and the transaction continues.

Example with XACT_ABORT ON

CREATE TABLE #Accounts
(
    AccountId INT PRIMARY KEY,
    Balance DECIMAL(12,2)
        CHECK (Balance >= 0)
);

INSERT INTO #Accounts
VALUES (1, 5000), (2, 3000);

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE #Accounts
    SET Balance = Balance - 1000
    WHERE AccountId = 1;

    -- Fails because balance cannot be negative
    UPDATE #Accounts
    SET Balance = -500
    WHERE AccountId = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_MESSAGE() AS ErrorMessage,
        XACT_STATE() AS TransactionState;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

SELECT *
FROM #Accounts;

Result

An error is raised because of the CHECK constraint:

The UPDATE statement conflicted with the CHECK constraint.

After rollback:

AccountId Balance
1 5000.00
2 3000.00

Even the first valid update is rolled back.

Recommended stored procedure pattern

CREATE PROCEDURE dbo.TransferAmount
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Related database operations

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;

Key points

  • Recommended for multi-statement transactions.

  • Prevents partially completed transactions after most runtime errors.

  • THROW honors XACT_ABORT.

  • Compile-time and certain special errors may behave differently.

3. SET TRANSACTION ISOLATION LEVEL

What it does

Controls how one transaction sees data modified by other concurrent transactions.

Syntax:

SET TRANSACTION ISOLATION LEVEL
{
    READ UNCOMMITTED
  | READ COMMITTED
  | REPEATABLE READ
  | SNAPSHOT
  | SERIALIZABLE
};

Isolation-level comparison

Level Dirty reads Non-repeatable reads Phantom rows
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Prevented Possible Possible
REPEATABLE READ Prevented Prevented Possible
SNAPSHOT Prevented Prevented Prevented within its transaction view
SERIALIZABLE Prevented Prevented Prevented

A. READ UNCOMMITTED

Allows reading data that another transaction has not yet committed.

Session 1

BEGIN TRANSACTION;

UPDATE dbo.Employees
SET Salary = 90000
WHERE EmployeeId = 101;

-- Do not commit yet

Session 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT Salary
FROM dbo.Employees
WHERE EmployeeId = 101;

Result

Session 2 may display:

90000

If Session 1 rolls back:

ROLLBACK;

the value read by Session 2 never became permanent. This is a dirty read.

B. READ COMMITTED

Prevents dirty reads and is the normal SQL Server default.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT Salary
FROM dbo.Employees
WHERE EmployeeId = 101;

Result

If another transaction has updated the row but not committed it, this query normally waits when lock-based READ COMMITTED is used.

Its exact behavior also depends on the database’s READ_COMMITTED_SNAPSHOT option.

C. REPEATABLE READ

Prevents another transaction from changing rows already read until the current transaction completes.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

SELECT Salary
FROM dbo.Employees
WHERE EmployeeId = 101;

-- Other sessions cannot update this row now.

SELECT Salary
FROM dbo.Employees
WHERE EmployeeId = 101;

COMMIT;

Both queries return the same salary. However, another transaction may insert new rows matching a range condition, producing phantom rows.

D. SNAPSHOT

Uses row versions to provide a transactionally consistent view.

First enable it:

ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT Salary
FROM dbo.Employees
WHERE EmployeeId = 101;

-- Another session may update and commit the row.

SELECT Salary
FROM dbo.Employees
WHERE EmployeeId = 101;

COMMIT;

Result

Both queries see the version that was committed when the snapshot transaction began accessing data.

E. SERIALIZABLE

Provides the strongest locking-based isolation.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT *
FROM dbo.Employees
WHERE Salary BETWEEN 50000 AND 80000;

-- Range is protected until COMMIT.

COMMIT;

Result

Other transactions cannot update qualifying rows or insert new rows into the protected key range until the transaction completes.

Key point

Higher isolation provides greater consistency but can reduce concurrency and increase blocking.

4. SET IMPLICIT_TRANSACTIONS

What it does

Controls whether SQL Server automatically starts a transaction for certain statements.

IMPLICIT_TRANSACTIONS ON

SET IMPLICIT_TRANSACTIONS ON;

UPDATE dbo.Employees
SET Salary = 75000
WHERE EmployeeId = 101;

SELECT @@TRANCOUNT AS TransactionCount;

Result

TransactionCount
1

SQL Server has started a transaction, but it remains open.

You must finish it:

COMMIT TRANSACTION;

or:

ROLLBACK TRANSACTION;

IMPLICIT_TRANSACTIONS OFF

SET IMPLICIT_TRANSACTIONS OFF;

UPDATE dbo.Employees
SET Salary = 76000
WHERE EmployeeId = 101;

SELECT @@TRANCOUNT AS TransactionCount;

Result

TransactionCount
0

The standalone update runs in autocommit mode.

Statements that can start an implicit transaction

Examples include:

  • INSERT

  • UPDATE

  • DELETE

  • SELECT accessing a table

  • CREATE

  • ALTER

  • DROP

  • GRANT

  • REVOKE

Main risk

If the developer forgets COMMIT or ROLLBACK, the open transaction can hold locks and block other users.

5. SET ANSI_NULLS

What it does

Controls how comparisons using NULL behave.

In modern SQL Server versions, ANSI_NULLS is effectively always ON.

Incorrect comparison

SET ANSI_NULLS ON;

SELECT *
FROM dbo.Employees
WHERE ManagerId = NULL;

Result

No rows

NULL means an unknown value. It cannot be compared using = or <>.

Correct comparison

SELECT *
FROM dbo.Employees
WHERE ManagerId IS NULL;

Result

Returns employees who do not have a manager.

For non-null values:

SELECT *
FROM dbo.Employees
WHERE ManagerId IS NOT NULL;

Important points

  • Always use IS NULL and IS NOT NULL.

  • ANSI_NULLS OFF is deprecated.

  • It must be ON for indexed views and indexed computed columns.

  • SQL Server stores this setting when some database objects are created.

6. SET QUOTED_IDENTIFIER

What it does

Controls how SQL Server interprets double quotation marks.

QUOTED_IDENTIFIER ON

SET QUOTED_IDENTIFIER ON;

SELECT
    "EmployeeName"
FROM dbo.Employees;

Result

"EmployeeName" is treated as a column identifier. It is equivalent to:

SELECT [EmployeeName]
FROM dbo.Employees;

String values must use single quotes:

SELECT 'Syed' AS EmployeeName;

Object name containing a space

SET QUOTED_IDENTIFIER ON;

CREATE TABLE dbo."Employee Details"
(
    "Employee Id" INT,
    "Employee Name" VARCHAR(100)
);

The double-quoted values are treated as identifiers.

QUOTED_IDENTIFIER OFF

SET QUOTED_IDENTIFIER OFF;

SELECT "Syed" AS EmployeeName;

Result

Syed

Here, "Syed" is treated as a string.

Recommendation

Keep it ON.

It is required for:

  • Indexed views

  • Indexes on computed columns

  • Filtered indexes

  • Some XML data type operations

It is a parse-time option, and its value is captured when a stored procedure is created or altered.

7. SET ARITHABORT

What it does

Controls whether SQL Server terminates a query when an arithmetic overflow or divide-by-zero error occurs.

ARITHABORT ON

SET ARITHABORT ON;

SELECT 100 / 0 AS Result;

Result

Divide by zero error encountered.

The statement is terminated.

Arithmetic overflow example

SET ARITHABORT ON;

DECLARE @Number TINYINT;

SET @Number = 300;

A TINYINT supports only 0 to 255.

Result

Arithmetic overflow error converting expression to data type tinyint.

Important points

  • Keep ARITHABORT ON for modern application queries.

  • It is required for indexed views and indexed computed columns.

  • Different ARITHABORT settings between SSMS and an application can result in different cached execution plans.

  • Its behavior also interacts with ANSI_WARNINGS and ARITHIGNORE.

8. SET LOCK_TIMEOUT

What it does

Determines how long a statement waits for a lock.

The value is specified in milliseconds.

SET LOCK_TIMEOUT 5000;

Meaning:

Wait for a maximum of five seconds.

Example

Session 1

BEGIN TRANSACTION;

UPDATE dbo.Employees
SET Salary = 80000
WHERE EmployeeId = 101;

-- Keep the transaction open

Session 2

SET LOCK_TIMEOUT 3000;

UPDATE dbo.Employees
SET Salary = 85000
WHERE EmployeeId = 101;

Result after approximately three seconds

Lock request time out period exceeded.

Check the current value:

SELECT @@LOCK_TIMEOUT AS LockTimeoutMilliseconds;

Result:

LockTimeoutMilliseconds
3000

Special values

Value Meaning
-1 Wait indefinitely; default
0 Do not wait
3000 Wait for three seconds

Reset:

SET LOCK_TIMEOUT -1;

Important point

A lock timeout is different from a command timeout:

  • LOCK_TIMEOUT is controlled by SQL Server.

  • Command timeout is usually controlled by the client or application.

9. SET DEADLOCK_PRIORITY

What it does

Determines which transaction SQL Server should prefer as a deadlock victim.

Syntax:

SET DEADLOCK_PRIORITY
{
    LOW
  | NORMAL
  | HIGH
  | integer
};

Integer range:

-10 to 10

Named values:

Name Numeric value
LOW -5
NORMAL 0
HIGH 5

Deadlock example

Assume two rows exist in Accounts.

Session 1

SET DEADLOCK_PRIORITY HIGH;

BEGIN TRANSACTION;

UPDATE dbo.Accounts
SET Balance = Balance - 100
WHERE AccountId = 1;

-- Session 2 locks AccountId 2

UPDATE dbo.Accounts
SET Balance = Balance + 100
WHERE AccountId = 2;

COMMIT;

Session 2

SET DEADLOCK_PRIORITY LOW;

BEGIN TRANSACTION;

UPDATE dbo.Accounts
SET Balance = Balance - 50
WHERE AccountId = 2;

-- Session 1 has locked AccountId 1

UPDATE dbo.Accounts
SET Balance = Balance + 50
WHERE AccountId = 1;

COMMIT;

The lock dependency is:

Session 1 holds Account 1 and waits for Account 2.
Session 2 holds Account 2 and waits for Account 1.

Result

SQL Server detects the deadlock and generally terminates Session 2 because it has LOW priority:

Transaction was deadlocked on lock resources and has been
chosen as the deadlock victim. Rerun the transaction.

Important points

  • Priority influences victim selection.

  • It does not prevent deadlocks.

  • If priorities are equal, SQL Server normally considers rollback cost.

  • Applications should catch deadlock error 1205 and retry safely.

10. SET STATISTICS IO

What it does

Displays the number of data pages SQL Server reads while executing a query.

SET STATISTICS IO ON;

SELECT
    EmployeeId,
    EmployeeName,
    Salary
FROM dbo.Employees
WHERE DepartmentId = 10;

SET STATISTICS IO OFF;

Example result in the Messages tab

Table 'Employees'.
Scan count 1,
logical reads 8,
physical reads 0,
read-ahead reads 0.

Important values

Value Meaning
Scan count Number of seek or scan operations started
Logical reads Pages read from the buffer cache
Physical reads Pages read from disk
Read-ahead reads Pages prefetched from disk
LOB logical reads Pages read for large objects

How to use it

Compare two queries:

SET STATISTICS IO ON;

-- Query 1
SELECT *
FROM dbo.Employees
WHERE DepartmentId = 10;

-- Query 2
SELECT EmployeeId, EmployeeName
FROM dbo.Employees
WHERE DepartmentId = 10;

SET STATISTICS IO OFF;

Generally, fewer logical reads indicate less data-access work. However, you must also consider CPU time, elapsed time and the execution plan.

11. SET STATISTICS TIME

What it does

Displays CPU and elapsed time used for query compilation and execution.

SET STATISTICS TIME ON;

SELECT *
FROM dbo.Employees
WHERE DepartmentId = 10;

SET STATISTICS TIME OFF;

Example result

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 5 ms, elapsed time = 12 ms.

Meaning

Measurement Meaning
CPU time Total processor time used
Elapsed time Actual wall-clock duration
Parse and compile time Time used to parse and compile the plan

If elapsed time is much greater than CPU time, the query may be waiting for:

  • Locks

  • Disk I/O

  • Network activity

  • Memory grants

  • Parallel workers

  • Other resources

Best troubleshooting combination

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Query to test

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

12. SET DATEFORMAT

What it does

Controls how SQL Server interprets date values supplied as character strings.

Common formats:

Format Order
dmy Day, month, year
mdy Month, day, year
ymd Year, month, day
ydm Year, day, month
myd Month, year, day
dym Day, year, month

dmy example

SET DATEFORMAT dmy;

SELECT CONVERT(DATE, '18/09/2026') AS ParsedDate;

Result

ParsedDate
2026-09-18

The input means:

18 = day
09 = month
2026 = year

mdy example

SET DATEFORMAT mdy;

SELECT CONVERT(DATE, '09/18/2026') AS ParsedDate;

Result:

ParsedDate
2026-09-18

Invalid interpretation

SET DATEFORMAT mdy;

SELECT CONVERT(DATE, '18/09/2026');

Result:

Conversion failed when converting date and/or time from character string.

There is no month 18.

Recommendation

Use typed parameters or an unambiguous format:

'20260918'

The displayed result may still appear as 2026-09-18 because SQL Server stores a DATE as a date value, not in the original input format.

13. SET DATEFIRST

What it does

Specifies the first day of the week.

Value First day
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday

Monday as the first day

September 21, 2026 is a Monday.

SET DATEFIRST 1;

SELECT
    @@DATEFIRST AS FirstDay,
    DATEPART(WEEKDAY, '20260921') AS WeekdayNumber;

Result

FirstDay WeekdayNumber
1 1

Sunday as the first day

SET DATEFIRST 7;

SELECT
    @@DATEFIRST AS FirstDay,
    DATEPART(WEEKDAY, '20260921') AS WeekdayNumber;

Result

FirstDay WeekdayNumber
7 2

Important point

The date remains Monday. Only its weekday number changes according to the configured first day.

14. SET LANGUAGE

What it does

Sets the language for the current session.

It can affect:

  • Month names

  • Day names

  • System messages

  • DATEFORMAT

  • DATEFIRST

English example

SET LANGUAGE us_english;

SELECT
    DATENAME(MONTH, '20260918') AS MonthName,
    DATENAME(WEEKDAY, '20260918') AS DayName,
    @@LANGUAGE AS CurrentLanguage;

Result

MonthName DayName CurrentLanguage
September Friday us_english

French example

SET LANGUAGE French;

SELECT
    DATENAME(MONTH, '20260918') AS MonthName,
    DATENAME(WEEKDAY, '20260918') AS DayName;

Result

MonthName DayName
septembre vendredi

List the installed languages:

EXEC sys.sp_helplanguage;

Important point

SET LANGUAGE may automatically change DATEFORMAT and DATEFIRST based on the chosen language.

15. SET ROWCOUNT

What it does

Limits the number of rows returned or affected by subsequent statements.

Limiting a SELECT

SET ROWCOUNT 3;

SELECT
    EmployeeId,
    EmployeeName
FROM dbo.Employees
ORDER BY EmployeeId;

Result

Only three rows are returned:

EmployeeId EmployeeName
1 Arun
2 Priya
3 Syed

Limiting an UPDATE

SET ROWCOUNT 3;

UPDATE dbo.Employees
SET Salary = Salary + 1000;

SELECT @@ROWCOUNT AS UpdatedRows;

SET ROWCOUNT 0;

Result

UpdatedRows
3

Only three employees are updated.

Resetting it

Always reset the option:

SET ROWCOUNT 0;

0 means there is no row limit.

Prefer TOP

For SELECT:

SELECT TOP (3)
    EmployeeId,
    EmployeeName
FROM dbo.Employees
ORDER BY EmployeeId;

For deterministic updates:

WITH EmployeesToUpdate AS
(
    SELECT TOP (3) *
    FROM dbo.Employees
    ORDER BY EmployeeId
)
UPDATE EmployeesToUpdate
SET Salary = Salary + 1000;

Why is TOP preferable?

  • The limit is visible in the statement.

  • It does not unexpectedly affect later statements.

  • The optimizer can consider the row goal.

  • It is clearer and safer.

Quick Revision Table

Statement Main purpose
NOCOUNT Controls rows-affected messages
XACT_ABORT Rolls back a transaction after most runtime errors
TRANSACTION ISOLATION LEVEL Controls concurrent read consistency
IMPLICIT_TRANSACTIONS Automatically starts transactions
ANSI_NULLS Controls NULL comparison behavior
QUOTED_IDENTIFIER Controls double-quoted identifiers
ARITHABORT Terminates queries on arithmetic errors
LOCK_TIMEOUT Limits lock-wait duration
DEADLOCK_PRIORITY Influences deadlock victim selection
STATISTICS IO Shows page-read information
STATISTICS TIME Shows CPU and elapsed time
DATEFORMAT Controls character-date interpretation
DATEFIRST Sets the first weekday
LANGUAGE Sets session language and related date defaults
ROWCOUNT Limits rows returned or affected

Recommended procedure defaults

CREATE PROCEDURE dbo.SampleProcedure
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Database operations

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;




Frequently Used @@ Functions in SQL Server

Names beginning with @@ are commonly called global variables, but Microsoft classifies many of them as system or configuration functions.

They return information about:

  • The previously executed statement

  • Current transaction

  • Current connection

  • Server configuration

  • Cursor operations

  • Stored-procedure execution

  • SQL Server version

The results shown below are examples. Actual values depend on your server, session and data.

Quick List

Function Purpose
@@ROWCOUNT Rows affected by the previous statement
@@ERROR Error number from the previous statement
@@TRANCOUNT Number of active transaction levels
@@IDENTITY Latest identity generated in the session
@@SPID Current session ID
@@VERSION SQL Server version information
@@SERVERNAME Configured SQL Server instance name
@@SERVICENAME SQL Server service/instance name
@@LANGUAGE Current session language
@@DATEFIRST First day of the week
@@LOCK_TIMEOUT Current lock timeout
@@OPTIONS Current session options as a bitmask
@@NESTLEVEL Current stored-procedure nesting level
@@PROCID Current stored procedure’s object ID
@@FETCH_STATUS Status of the previous cursor fetch
@@CURSOR_ROWS Number of rows in the last opened cursor
@@DBTS Current database rowversion value
@@MAX_CONNECTIONS Maximum allowed user connections
@@MAX_PRECISION Maximum decimal/numeric precision
@@CONNECTIONS Connections attempted since startup

1. @@ROWCOUNT

What it returns

Returns the number of rows affected or read by the immediately preceding statement.

Example: UPDATE

UPDATE dbo.Employees
SET Salary = Salary + 1000
WHERE DepartmentId = 10;

SELECT @@ROWCOUNT AS UpdatedRows;

Result

If five employees were updated:

UpdatedRows
5

Example: SELECT

SELECT *
FROM dbo.Employees
WHERE DepartmentId = 10;

SELECT @@ROWCOUNT AS SelectedRows;

If five records were returned:

SelectedRows
5

Important warning

Read @@ROWCOUNT immediately because the next statement can change it.

Correct:

UPDATE dbo.Employees
SET Salary = Salary + 1000
WHERE DepartmentId = 10;

DECLARE @AffectedRows INT = @@ROWCOUNT;

SELECT @AffectedRows AS AffectedRows;

Incorrect:

UPDATE dbo.Employees
SET Salary = Salary + 1000
WHERE DepartmentId = 10;

PRINT 'Update completed';

SELECT @@ROWCOUNT;

PRINT changes the value, so the original update count is lost.

2. @@ERROR

What it returns

Returns the error number produced by the immediately preceding statement.

  • 0 means no error.

  • A non-zero value means an error occurred.

Example

UPDATE dbo.Employees
SET Salary = 'Invalid Salary'
WHERE EmployeeId = 101;

SELECT @@ERROR AS ErrorNumber;

Possible result:

Error converting data type varchar to decimal.

Followed by an error number such as:

ErrorNumber
8114

Correct legacy pattern

UPDATE dbo.Employees
SET Salary = 75000
WHERE EmployeeId = 101;

DECLARE @ErrorNumber INT = @@ERROR;
DECLARE @AffectedRows INT = @@ROWCOUNT;

SELECT
    @ErrorNumber AS ErrorNumber,
    @AffectedRows AS AffectedRows;

Modern recommendation

Prefer TRY...CATCH:

BEGIN TRY
    UPDATE dbo.Employees
    SET Salary = 'Invalid Salary'
    WHERE EmployeeId = 101;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

@@ERROR is still valid, but TRY...CATCH provides more complete error information.

3. @@TRANCOUNT

What it returns

Returns the number of active transaction levels in the current session.

Example

SELECT @@TRANCOUNT AS BeforeTransaction;

BEGIN TRANSACTION;

SELECT @@TRANCOUNT AS AfterFirstBegin;

BEGIN TRANSACTION;

SELECT @@TRANCOUNT AS AfterSecondBegin;

COMMIT TRANSACTION;

SELECT @@TRANCOUNT AS AfterCommit;

ROLLBACK TRANSACTION;

SELECT @@TRANCOUNT AS AfterRollback;

Result

Stage @@TRANCOUNT
Before transaction 0
After first BEGIN 1
After second BEGIN 2
After one COMMIT 1
After ROLLBACK 0

Important behavior

SQL Server does not create completely independent nested transactions. A full ROLLBACK TRANSACTION without a savepoint normally rolls back the complete transaction and resets @@TRANCOUNT to zero.

Common usage

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

4. @@IDENTITY

What it returns

Returns the last identity value generated in the current session, across all scopes.

Assume:

CREATE TABLE dbo.Employees
(
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName VARCHAR(100)
);

Example

INSERT INTO dbo.Employees(EmployeeName)
VALUES ('Syed');

SELECT @@IDENTITY AS NewEmployeeId;

Result

If the generated ID is 101:

NewEmployeeId
101

Important warning

@@IDENTITY can return an identity value generated by a trigger.

For example:

Insert employee
    ↓
Employee trigger inserts audit row
    ↓
@@IDENTITY may return the AuditId

Preferred alternatives

Use SCOPE_IDENTITY():

INSERT INTO dbo.Employees(EmployeeName)
VALUES ('Syed');

SELECT SCOPE_IDENTITY() AS NewEmployeeId;

Or use the OUTPUT clause:

INSERT INTO dbo.Employees(EmployeeName)
OUTPUT INSERTED.EmployeeId
VALUES ('Syed');

Comparison

Method Behavior
@@IDENTITY Last identity in the session, including triggers
SCOPE_IDENTITY() Last identity in the current session and scope
IDENT_CURRENT('Table') Last identity for a specified table across all sessions
OUTPUT INSERTED.Id Directly returns inserted identity values

5. @@SPID

What it returns

Returns the session ID of the current SQL Server connection.

Example

SELECT @@SPID AS CurrentSessionId;

Example result

CurrentSessionId
57

Usage

It is useful for:

  • Identifying the current connection

  • Troubleshooting blocking

  • Monitoring requests

  • Reading session DMVs

  • Correlating logs

SELECT *
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Do not terminate the current session using:

KILL @@SPID;

KILL expects a session ID that is not the executing session.

6. @@VERSION

What it returns

Returns detailed SQL Server version and operating-system information.

Example

SELECT @@VERSION AS VersionInformation;

Example result

Microsoft SQL Server 2025 ...
Developer Edition (64-bit)
on Windows Server ...

For structured version information, use:

SELECT
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;

This is easier for applications to process than the long @@VERSION string.

7. @@SERVERNAME

What it returns

Returns SQL Server’s locally configured server name.

Example

SELECT @@SERVERNAME AS ServerName;

Example result

ServerName
SERVER01\SQL2025

Important point

@@SERVERNAME can be incorrect after a machine rename if SQL Server’s internal server-name metadata was not updated.

Compare with:

SELECT
    @@SERVERNAME AS ConfiguredServerName,
    SERVERPROPERTY('ServerName') AS CurrentServerName;

8. @@SERVICENAME

What it returns

Returns the name of the SQL Server service for the current instance.

Example

SELECT @@SERVICENAME AS ServiceName;

Possible results

Default instance:

MSSQLSERVER

Named instance:

SQL2025

This is useful when one machine hosts multiple SQL Server instances.

9. @@LANGUAGE

What it returns

Returns the language currently configured for the session.

Example

SET LANGUAGE us_english;

SELECT @@LANGUAGE AS CurrentLanguage;

Result

us_english

Change it:

SET LANGUAGE French;

SELECT
    @@LANGUAGE AS CurrentLanguage,
    DATENAME(MONTH, '20260918') AS MonthName;

Result

CurrentLanguage MonthName
Français septembre

The exact returned language label depends on the installed SQL Server language metadata.

10. @@DATEFIRST

What it returns

Returns the current first day of the week as a number from 1 through 7.

Example

SET DATEFIRST 1;

SELECT @@DATEFIRST AS FirstDayOfWeek;

Result

FirstDayOfWeek
1

1 means Monday.

SET DATEFIRST 7;

SELECT @@DATEFIRST AS FirstDayOfWeek;

Result:

FirstDayOfWeek
7

7 means Sunday.

11. @@LOCK_TIMEOUT

What it returns

Returns the current lock timeout in milliseconds.

Example

SET LOCK_TIMEOUT 5000;

SELECT @@LOCK_TIMEOUT AS LockTimeoutMilliseconds;

Result

LockTimeoutMilliseconds
5000

Meaning:

Wait for a lock for a maximum of five seconds.

Reset:

SET LOCK_TIMEOUT -1;

Result:

LockTimeoutMilliseconds
-1

-1 means wait indefinitely.

12. @@OPTIONS

What it returns

Returns a bitmask representing the active session SET options.

Example

SELECT @@OPTIONS AS OptionsBitmask;

Example result

OptionsBitmask
5496

The numeric value represents multiple options combined.

Check a specific option

For example, the NOCOUNT bit is 512:

IF (512 & @@OPTIONS) = 512
    SELECT 'NOCOUNT is ON' AS Result;
ELSE
    SELECT 'NOCOUNT is OFF' AS Result;

Possible result:

NOCOUNT is ON

Check XACT_ABORT, whose bit is 16384:

IF (16384 & @@OPTIONS) = 16384
    SELECT 'XACT_ABORT is ON' AS Result;
ELSE
    SELECT 'XACT_ABORT is OFF' AS Result;

Common option bits

Option Bit value
DISABLE_DEF_CNST_CHK 1
IMPLICIT_TRANSACTIONS 2
CURSOR_CLOSE_ON_COMMIT 4
ANSI_WARNINGS 8
ANSI_PADDING 16
ANSI_NULLS 32
ARITHABORT 64
ARITHIGNORE 128
QUOTED_IDENTIFIER 256
NOCOUNT 512
ANSI_NULL_DFLT_ON 1024
ANSI_NULL_DFLT_OFF 2048
CONCAT_NULL_YIELDS_NULL 4096
NUMERIC_ROUNDABORT 8192
XACT_ABORT 16384

For readable session settings, use:

DBCC USEROPTIONS;

13. @@NESTLEVEL

What it returns

Returns the current stored-procedure nesting level.

Example

CREATE OR ALTER PROCEDURE dbo.ProcedureB
AS
BEGIN
    SELECT @@NESTLEVEL AS ProcedureBNestLevel;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ProcedureA
AS
BEGIN
    SELECT @@NESTLEVEL AS ProcedureANestLevel;

    EXEC dbo.ProcedureB;
END;
GO

EXEC dbo.ProcedureA;

Result

First result:

ProcedureANestLevel
1

Second result:

ProcedureBNestLevel
2

SQL Server permits stored procedures to be nested up to a limit. Excessive nesting usually indicates difficult-to-maintain design.

14. @@PROCID

What it returns

Returns the object ID of the currently executing T-SQL module, such as a stored procedure.

Example

CREATE OR ALTER PROCEDURE dbo.ShowCurrentProcedure
AS
BEGIN
    SELECT
        @@PROCID AS ProcedureObjectId,
        OBJECT_SCHEMA_NAME(@@PROCID) AS SchemaName,
        OBJECT_NAME(@@PROCID) AS ProcedureName;
END;
GO

EXEC dbo.ShowCurrentProcedure;

Example result

ProcedureObjectId SchemaName ProcedureName
123456789 dbo ShowCurrentProcedure

Usage

Useful for generic logging:

INSERT INTO dbo.ErrorLog
(
    ProcedureName,
    ErrorMessage
)
VALUES
(
    OBJECT_SCHEMA_NAME(@@PROCID)
        + '.'
        + OBJECT_NAME(@@PROCID),
    ERROR_MESSAGE()
);

Outside an executing module, @@PROCID may return NULL.

15. @@FETCH_STATUS

What it returns

Returns the status of the most recent cursor FETCH.

Values:

Value Meaning
0 Fetch succeeded
-1 Fetch failed or moved beyond the result set
-2 Fetched row is missing
-9 Cursor is not performing a fetch operation

Example

DECLARE EmployeeCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT EmployeeName
FROM dbo.Employees
ORDER BY EmployeeId;

DECLARE @EmployeeName VARCHAR(100);

OPEN EmployeeCursor;

FETCH NEXT FROM EmployeeCursor
INTO @EmployeeName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @EmployeeName AS EmployeeName;

    FETCH NEXT FROM EmployeeCursor
    INTO @EmployeeName;
END;

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Result

Employee names are returned one at a time until:

@@FETCH_STATUS = -1

That means there are no more rows.

Important point

@@FETCH_STATUS is connection-wide and reflects the last cursor fetch. Save it immediately if nested procedures or other cursors may execute.

16. @@CURSOR_ROWS

What it returns

Returns the number of qualifying rows in the most recently opened cursor.

Example

DECLARE EmployeeCursor CURSOR STATIC
FOR
SELECT EmployeeId
FROM dbo.Employees
WHERE DepartmentId = 10;

OPEN EmployeeCursor;

SELECT @@CURSOR_ROWS AS CursorRows;

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Example result

CursorRows
5

Possible behavior:

  • Positive value: Cursor is fully populated and contains that many rows.

  • Negative value: Cursor is being populated asynchronously; the absolute value shows rows currently available.

  • 0: No qualifying rows or no cursor is open.

  • -1: Dynamic cursor whose exact row count is not known.

17. @@DBTS

What it returns

Returns the current rowversion value for the current database.

Assume:

CREATE TABLE dbo.Products
(
    ProductId INT PRIMARY KEY,
    ProductName VARCHAR(100),
    VersionNumber ROWVERSION
);

Example

SELECT @@DBTS AS CurrentDatabaseTimestamp;

Example result

0x00000000000007D3

Insert or update a row:

UPDATE dbo.Products
SET ProductName = 'Laptop Pro'
WHERE ProductId = 1;

SELECT @@DBTS AS NewDatabaseTimestamp;

Possible result:

0x00000000000007D4

Important points

  • ROWVERSION is not a date or time.

  • It is an automatically generated binary version number.

  • It is useful for optimistic concurrency.

  • TIMESTAMP is an old synonym for ROWVERSION and should be avoided.

18. @@MAX_CONNECTIONS

What it returns

Returns the maximum number of simultaneous user connections allowed on the SQL Server instance.

Example

SELECT @@MAX_CONNECTIONS AS MaximumConnections;

Example result

MaximumConnections
32767

This is the configured theoretical maximum, not the number of active connections.

To see current user sessions:

SELECT COUNT(*) AS CurrentUserSessions
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

19. @@MAX_PRECISION

What it returns

Returns the maximum precision supported by DECIMAL and NUMERIC.

Example

SELECT @@MAX_PRECISION AS MaximumPrecision;

Result

MaximumPrecision
38

Therefore, the largest supported declaration is:

DECIMAL(38, scale)

Example:

DECLARE @Amount DECIMAL(38,2);

SET @Amount = 999999999999999999999999999999999999.99;

The total precision cannot exceed 38 digits.

20. @@CONNECTIONS

What it returns

Returns the number of connection attempts made since SQL Server was last started.

Example

SELECT @@CONNECTIONS AS ConnectionAttempts;

Example result

ConnectionAttempts
48520

Important point

It is cumulative since startup. It does not represent current active connections.

Current active connections can be examined through:

SELECT COUNT(*) AS CurrentConnections
FROM sys.dm_exec_connections;

Frequently Used Server Statistics

The following are more useful for DBAs and monitoring than normal application queries.

Function Purpose
@@CPU_BUSY CPU work time since startup, in ticks
@@IDLE Idle time since startup, in ticks
@@IO_BUSY I/O operation time since startup, in ticks
@@PACK_RECEIVED Network packets received since startup
@@PACK_SENT Network packets sent since startup
@@PACKET_ERRORS Network packet errors since startup
@@TOTAL_READ Disk reads since startup
@@TOTAL_WRITE Disk writes since startup
@@TOTAL_ERRORS Disk read/write errors since startup
@@TIMETICKS Microseconds per tick

Example

SELECT
    @@CONNECTIONS AS ConnectionAttempts,
    @@PACK_RECEIVED AS PacketsReceived,
    @@PACK_SENT AS PacketsSent,
    @@TOTAL_READ AS TotalReads,
    @@TOTAL_WRITE AS TotalWrites;

Example result:

ConnectionAttempts PacketsReceived PacketsSent TotalReads TotalWrites
48520 950000 1200000 340000 85000

Modern monitoring normally uses Dynamic Management Views, performance counters and observability tools instead of relying only on these cumulative functions.

Most Important for Interviews

1. @@ROWCOUNT

Returns rows affected by the previous statement.

UPDATE dbo.Employees
SET Salary = 75000
WHERE DepartmentId = 10;

SELECT @@ROWCOUNT;

2. @@TRANCOUNT

Returns the current transaction nesting count.

SELECT @@TRANCOUNT;

3. @@ERROR

Returns the previous statement’s error number, but modern code should prefer TRY...CATCH.

SELECT @@ERROR;

4. @@SPID

Returns the current session ID.

SELECT @@SPID;

5. @@IDENTITY

Returns the latest identity created in the session, including trigger scope. Prefer SCOPE_IDENTITY() or OUTPUT INSERTED.Id.

SELECT @@IDENTITY;

6. @@FETCH_STATUS

Returns the previous cursor fetch status.

WHILE @@FETCH_STATUS = 0

7. @@VERSION

Returns SQL Server version information.

SELECT @@VERSION;

8. @@OPTIONS

Returns active session options as a bitmask.

SELECT @@OPTIONS;

Key points

  • Values such as @@ROWCOUNT and @@ERROR must be captured immediately.

  • @@TRANCOUNT helps detect open transactions.

  • @@IDENTITY can return an identity created by a trigger.

  • Prefer SCOPE_IDENTITY() or OUTPUT for inserted IDs.

  • @@SPID identifies the current SQL Server session.

  • @@OPTIONS is a bitmask; use DBCC USEROPTIONS for a readable list.

  • @@DBTS is a version counter, not a date or time.

  • Server-statistics functions normally contain values accumulated since SQL Server startup.

 

 

← Back to Article List