Every user session in SQL Server comes with a number of “flags”, or settings, that you can alter to modify the way SQL Server behaves in different aspects. Some are fairly straight-forward, which others are old legacy options or even completely deprecated and have no actual use.
User settings
Here’s a non-comprehensive list of the different settings available. I’ve taken the liberty of removing some of the most obscure or deprecated ones for the sake of readability.
IMPLICIT_TRANSACTIONS
When set to ON, a new SQL transaction is automatically created whenever you issue a T-SQL statement, unless you are already in a transaction. Before terminating your connection, you need to manually commit or roll back the current transaction. This resembles the behaviour of earlier versions of Oracle.
Recommendation: Leave OFF. There are very few cases where you would actually want this setting turned on. Instead, make a habit of explicitly managing transactions in your code. This gives you greater control, better performance and simplifies troubleshooting considerably.
CURSOR_CLOSE_ON_COMMIT
When set to ON, SQL Server will automatically close any open cursors whenever you commit a transaction.
Recommendation: Leave this flag OFF. Instead, properly close and deallocate all your cursors in your code. Better yet, don’t use cursors at all.
ANSI_WARNINGS
This setting controls some of the warning messages displayed. When set to ON, warnings are displayed when aggregates include NULL values, and operations that generate division-by-zero or overflows will fail.
Warning: Null value is eliminated by an aggregate or other SET operation.
When OFF, no warning will be displayed for null aggregates, and overflows and div/0 operations will return NULL values instead.
Recommendation: Leave this setting ON. If you absolutely don’t want the NULL aggregate warnings, wrap the aggregate values in ISNULL() where applicable.
ANSI_PADDING
Originally designed ages ago, before there were variable-length character datatypes (varchar and varbinary in SQL Server), this flag controls if string values are padded with trailing spaces. When ON, string values stored in char columns are padded, and any trailing spaces in a varchar column are preserved. When OFF, string values in char columns are trimmed, but so are string values in varchar columns, where a trailing space may actually be intentional.
Recommendation: Leave this setting ON. Use varchar columns for variable-length data (and optionally char for fixed-length values).
ANSI_NULLS
This setting tells SQL Server to handle comparison operators on NULL values in accordance to ISO standards. This setting will be deprecated in future versions of SQL Server.
Recommendation: Leave this setting ON. Future versions will return an error when set to OFF.
ARITHABORT
When ON, a query will terminate with an error if there is an overflow or division by zero.
Recommendation: Leave this setting ON. Turning it OFF can lead to unexpected results, and may also affect your query plans negatively because not all optimization options are available to SQL Server. Instead, fix your query using NULLIF() to eliminate division by zero.
QUOTED_IDENTIFIER
Controls whether quotes (“) are used to signify identifiers (i.e. column or object names) or string values in T-SQL.
Recommendation: Leave this ON.
NOCOUNT
When set to ON, SQL Server will not display “(n row(s) affected)” messages. Those messages may in some situations act as their own “recordsets” of sorts to some database providers, which may affect how returned data is received by a calling application.
Recommendation: The use of this setting varies with your specific application, but generally I would recommend that you to set this flag to ON in stored procedures and other modules, particularly in DML triggers.
CONCAT_NULL_YIELDS_NULL
When ON, concatenating a NULL value with another value will always yield NULL.
Recommendation: Leave this ON, and handle your NULLs using ISNULL() or COALESCE().
XACT_ABORT
One of my favourites, pronounced “transact abort”. When set to ON, a batch will terminate and the transaction will be rolled back if there is an error raised in one of the statements in a batch.
Recommendation: Set this to ON if you have a a long sequence of statements within a transaction and you want to make sure everything stops and rolls back if something goes sideways.
Flags required for indexed views
Working with indexed views or indexes on computed columns requires the following settings:
ARITHABORT ON
CONCAT_NULL_YIELDS_NULL ON
QUOTED_IDENTIFIER ON
ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
NUMERIC_ROUNDABORT OFF
Viewing your current settings
The @@OPTIONS system variable can be used to view the current session’s settings. It works as a bitmap, where one flag/setting has one bit.
The following query uses the “bitwise and” function (“&” in T-SQL) to find each flag. The SIGN() function returns 1 for all positive values and 0 for zero-values, which makes it perfect for this application.
SELECT settings.configuration,
(CASE settings.setting
WHEN 0 THEN 'OFF'
WHEN 1 THEN 'ON'
END) AS setting
FROM (VALUES ('DISABLE_DEF_CNST_CHK', SIGN(@@OPTIONS&1)),
('IMPLICIT_TRANSACTIONS', SIGN(@@OPTIONS&2)),
('CURSOR_CLOSE_ON_COMMIT', SIGN(@@OPTIONS&4)),
('ANSI_WARNINGS', SIGN(@@OPTIONS&8)),
('ANSI_PADDING', SIGN(@@OPTIONS&16)),
('ANSI_NULLS', SIGN(@@OPTIONS&32)),
('ARITHABORT', SIGN(@@OPTIONS&64)),
('ARITHIGNORE', SIGN(@@OPTIONS&128)),
('QUOTED_IDENTIFIER', SIGN(@@OPTIONS&256)),
('NOCOUNT', SIGN(@@OPTIONS&512)),
('ANSI_NULL_DFLT_ON', SIGN(@@OPTIONS&1024)),
('ANSI_NULL_DFLT_OFF', SIGN(@@OPTIONS&2048)),
('CONCAT_NULL_YIELDS_NULL', SIGN(@@OPTIONS&4096)),
('NUMERIC_ROUNDABORT', SIGN(@@OPTIONS&8192)),
('XACT_ABORT', SIGN(@@OPTIONS&16384))
) AS settings(configuration, setting);
In fact, there’s a system table in the master database called spt_values that contains these flags (among lots of other stuff), so this query can be simplified a tad:
SELECT [name] AS configuration,
(CASE SIGN(@@OPTIONS&number)
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
END) AS setting
FROM master.dbo.spt_values
WHERE [type]='SOP' AND number>0
ORDER BY number;
Want more? There are more than a hundred other articles on different T-SQL topics. And make sure to like or subscribe to the Facebook page to get a notification of new posts.
1 comment