SQL Server session settings quietly change how your queries parse, compare, and report. A few of them also decide whether SQL Server will use a filtered index, a view indexed with SCHEMABINDING, or even generate a plan at all. Here is a reference to the ones worth knowing, with the current behavior on SQL Server 2019 and later.
- ANSI_NULLS
- QUOTED_IDENTIFIER
- ARITHABORT
- ANSI_DEFAULTS
- ANSI_WARNINGS
- DATEFIRST
- DATEFORMAT
- NOCOUNT
- NOEXEC
- IDENTITY_INSERT
- IMPLICIT_TRANSACTIONS
- LANGUAGE
ANSI_NULLS
– Syntax
SET ANSI_NULLS { ON | OFF }– Explanation
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to UNKNOWN — not FALSE, as the older documentation phrased it. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are non-null values in the column. Use IS NULL and IS NOT NULL to test for nulls — that works regardless of the setting.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard: WHERE column_name = NULL returns the rows with null values, and <> NULL returns the non-null rows.
Important (current behavior): SET ANSI_NULLS OFF and the database option ANSI_NULLS_OFF are deprecated and will not be supported in a future release. ODBC and OLE DB clients (including every modern driver) set ANSI_NULLS to ON automatically, and SQL Server rejects CREATE statements on filtered indexes or indexed views compiled with ANSI_NULLS OFF. Treat it as permanently ON.
ARITHABORT
– Syntax
SET ARITHABORT { ON | OFF }– Explanation
When SET ARITHABORT is ON, an arithmetic error (overflow, divide by zero) during query execution terminates the query or batch. If the error occurs inside a transaction, the transaction is rolled back. When OFF and ANSI_WARNINGS is also OFF, SQL Server instead produces NULL for the offending value and keeps going.
Worth knowing: ARITHABORT ON is the default in Management Studio but client libraries have historically varied, and since a query compiled with ARITHABORT ON gets a different cached plan from one compiled with OFF, inconsistent client settings were a classic source of the “fast in SSMS, slow in the app” plan-cache problem — today largely superseded by parameter sniffing issues, but worth remembering when reading old troubleshooting notes.
ANSI_DEFAULTS
– Syntax
SET ANSI_DEFAULTS { ON | OFF }– Explanation
When enabled (ON), this option enables a group of SQL-92 settings: ANSI_NULLS, CURSOR_CLOSE_ON_COMMIT, ANSI_NULL_DFLT_ON, IMPLICIT_TRANSACTIONS, ANSI_PADDING, QUOTED_IDENTIFIER and ANSI_WARNINGS. ODBC and OLE DB turn it on by default. In practice it is better to set the individual options explicitly than to rely on the group switch.
ANSI_WARNINGS
– Syntax
SET ANSI_WARNINGS { ON | OFF }– Explanation
When SET ANSI_WARNINGS is ON, aggregate functions hitting null values (SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, COUNT) generate a warning message, and divide-by-zero and arithmetic overflow errors roll back the statement and raise an error. When OFF, those errors instead return NULL and only produce a warning (unless ARITHABORT is also ON).
DATEFIRST
– Syntax
SET DATEFIRST { number | @number_var }– Explanation
An integer indicating the first day of the week:
| number | First day of the week is |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 (default, U.S. English) | Sunday |
Use the @@DATEFIRST function to check the current setting of SET DATEFIRST. The setting applies at run time, not parse time. If you need week-boundary logic that does not depend on the session language (US English defaults to 7/Sunday, most European languages to 1/Monday), compute the week start explicitly instead of relying on DATEFIRST.
NOCOUNT
– Syntax
SET NOCOUNT { ON | OFF }– Explanation
When SET NOCOUNT is ON, the DONE_IN_PROC message with the number of rows affected by each statement is not sent to the client. It is a standard performance practice for stored procedures: it removes network chatter in multi-statement procs (though each message is tiny, and 1 for a single statement, so only measure a difference on chatty procedures). Note that the setting does not affect @@ROWCOUNT, which you can still use in the procedure itself.
NOEXEC
– Syntax
SET NOEXEC { ON | OFF }– Explanation
When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them — useful to syntax-check a script for misspellings and reference errors before running it. The related SET PARSEONLY ON goes one step less: it only parses, without compiling, so it also skips checking object existence.
DATEFORMAT
– Syntax
SET DATEFORMAT { format | @format_var }– Explanation
Sets the order of the dateparts (ymd, ydm, myd, dmy, etc.) for interpreting character date strings. Example:
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/98';
SELECT @datevar;
GOModern note: prefer the unambiguous ISO formats for literals: ‘yyyymmdd’ (or ‘yyyyMMdd HH:mm:ss’) for datetime, and the full ISO 8601 ‘yyyy-MM-ddTHH:mm:ss’ which DATEFORMAT never touches. The classic trap is that ‘yyyy-mm-dd’ is interpreted according to the session language on datetime — use the date type or ISO 8601 to be safe.
IDENTITY_INSERT
– Syntax
SET IDENTITY_INSERT [ database.[ owner. ] ] { table } { ON | OFF }– Explanation
When SET IDENTITY_INSERT is ON you can insert explicit values into an identity column. If the inserted value is larger than the current identity value for the table, SQL Server automatically uses the new value as the current identity.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40));
GO
SET IDENTITY_INSERT products ON;
GO
-- succeeds only while IDENTITY_INSERT is ON
INSERT INTO products (id, product) VALUES (3, 'garden shovel');
GO
SET IDENTITY_INSERT products OFF;
GOWatch out: at any time only one table per session can have IDENTITY_INSERT ON, and the permission required is ALTER on the table. Remember to turn it OFF when you are done.
IMPLICIT_TRANSACTIONS (the original post had a typo — “IMPLICITY_TRANSACTIONS”)
– Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }– Explanation
When a connection is in implicit transaction mode and not currently in a transaction, executing any of the following statements automatically starts one: ALTER TABLE, FETCH, REVOKE, CREATE, GRANT, SELECT, DELETE, INSERT, TRUNCATE TABLE, DROP, OPEN, UPDATE. You must COMMIT or ROLLBACK yourself, or the transaction (and its locks) stays open.
ANSI_PADDING is worth mentioning here because it used to ride along with ANSI_DEFAULTS: it governed how trailing spaces in varchar and trailing zeros in varbinary were stored. It is deprecated since SQL Server 2012 and always behaves as ON in current versions.
LANGUAGE
– Syntax
SET LANGUAGE { [ N ] 'language' | @language_var }– Explanation
The language name as stored in the sys.syslanguages compatibility view (still the documented source in current SQL Server and Azure SQL Database; query it via sys.sp_helplanguage if you prefer a stored procedure). Use N’language’ for a Unicode value; a variable must be of type sysname. SET LANGUAGE specifies the language environment for the session: it determines datetime formats (together with DATEFIRST/DATEFORMAT) and the language of system messages. Note that SET LANGUAGE also implicitly sets DATEFORMAT to the default for that language.
A practical tip to close this out: the SET options that matter most for correctness (ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ARITHABORT) are stored in the plan cache as part of the compiled plan. If you ever see a query behave differently in Management Studio versus the application, compare those four settings first with the sys.dm_exec_plan_attributes DMV before blaming statistics.