T-SQL SET Statements

The SET statement in Transact-SQL (T-SQL) is used to change the behavior of the current session for specific options. These options affect how SQL Server processes queries and returns information.

Commonly Used SET Options

ANSI_NULLS

This option controls whether string comparisons with NULL evaluate to TRUE. When SET ANSI_NULLS ON, comparing any expression with NULL using comparison operators (=, !=, <, >, <=, >=) returns UNKNOWN, not TRUE or FALSE.

-- Default is ON in SQL Server
SET ANSI_NULLS ON;
-- SET ANSI_NULLS OFF;

QUOTED_IDENTIFIER

This option controls whether double quotation marks can be used to delimit identifiers (like table names or column names) and whether they are treated as literals. When SET QUOTED_IDENTIFIER ON, double quotes delimit identifiers.

-- Default is ON in SQL Server
SET QUOTED_IDENTIFIER ON;
-- SET QUOTED_IDENTIFIER OFF;

NUMERIC_ROUNDABORT

This option determines how errors are generated during rounding. When SET NUMERIC_ROUNDABORT ON, if an expression is evaluated that results in a loss of precision or overflow, an error is raised. If it's OFF (default), these rounding errors are ignored, and the results are truncated or rounded.

-- Default is OFF in SQL Server
SET NUMERIC_ROUNDABORT OFF;
-- SET NUMERIC_ROUNDABORT ON;

TEXTSIZE

This option sets the maximum size, in bytes, of character strings returned by the TEXT or NTEXT data types. The default is 4MB.

-- Set TEXTSIZE to 10MB
SET TEXTSIZE 10485760;
-- View current setting
SELECT @@TEXTSIZE;

DATEFIRST

This option sets the first day of the week for the session. The default is determined by the server's language setting (often Sunday).

-- Set Monday as the first day of the week
SET DATEFIRST 1;
-- Set Sunday as the first day of the week
-- SET DATEFIRST 7;

IDENTITY_INSERT

This option allows you to specify whether explicit values for an identity column can be inserted. This is typically used when migrating data or performing specific data manipulation tasks.

-- Allow explicit insertion into the 'MyTable' table's identity column
SET IDENTITY_INSERT MyTable ON;

-- Perform INSERT statement here...

-- Disable explicit insertion
SET IDENTITY_INSERT MyTable OFF;

Setting Options Globally and for Specific Queries

Most SET options affect the current session. However, some can be set globally for the server using `sp_configure` or can be applied temporarily to a specific query using the WITH clause in `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statements.

-- Applying SET options to a specific query
SELECT *
FROM MyTable
WITH (ROWLOCK, XLOCK)
WHERE SomeColumn = 'SomeValue';

Checking Current Settings

You can check the current values of various session settings using the @@<option_name> global variable functions.

SELECT @@VERSION, @@LANGUAGE, @@SERVERNAME, @@TEXTSIZE;

Related Topics