SET (Transact‑SQL)

The SET statement configures various execution settings for a session or the current query batch.

Syntax

SET option { ON | OFF }
SET option { ON | OFF | value }
SET option = { ON | OFF | value }

Options

ANSI_NULLS – Controls null comparison semantics.
ANSI_WARNINGS – Controls truncation and divide‑by‑zero warnings.
ARITHABORT – Terminates a query when an arithmetic overflow or divide‑by‑zero occurs.
NOCOUNT – Stops the message that shows the count of rows affected.
QUOTED_IDENTIFIER – Determines how quoted strings are interpreted.
… and many more. See SET Options for a full list.

Parameters

Parameter	Description
`option`	The session setting to configure. Must be a valid SET option.
`ON` / `OFF`	Enables or disables the option.
`value`	Option‑specific value (e.g., `SET DEADLOCK_PRIORITY LOW`).

Remarks

SET statements affect the current session or batch. Some options persist across batches, while others are limited to the scope of a stored procedure.

Changing certain settings can affect query plan reuse and execution performance. Review the performance best practices before modifying default values.

Examples

-- Turn off row count messages
SET NOCOUNT ON;

-- Enable ANSI_NULLS for proper null handling
SET ANSI_NULLS ON;

-- Set the transaction isolation level to READ COMMITTED SNAPSHOT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT;

-- Example: Change deadlock priority for the session
SET DEADLOCK_PRIORITY LOW;

SELECT *
FROM dbo.Orders
WHERE OrderDate > '2024-01-01';