A couple of years ago, I wrote an article which demonstrated that the SET options defined for a connection influence query execution and query results. One of the questions that came up in the research was how to determine which SET options are being used for a given session. It was then when I learnt about the system function – SESSIONPROPERTY().

The SESSIONPROPERTY() function can be used to return the current session value of the following SET options:

• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• NUMERIC_ROUNDABORT
• QUOTED_IDENTIFIER

Here’s an example:

SELECT SESSIONPROPERTY('ANSI_NULLS') AS IsAnsiNullsOn,
       SESSIONPROPERTY('ANSI_PADDING') AS IsAnsiPaddingOn,
       SESSIONPROPERTY('ANSI_WARNINGS') AS IsAnsiWarningsOn,
       SESSIONPROPERTY('ARITHABORT') AS IsArithAbortOn,
       SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS IsConcatNullYieldsNull,
       SESSIONPROPERTY('NUMERIC_ROUNDABORT') AS IsNumericRoundabortOn,
       SESSIONPROPERTY('QUOTED_IDENTIFIER') AS IsQuotedIdentifierOn;
GO

Output demonstrating the usage of SESSIONPROPERTY() system function

Please do keep in mind that the SET options take effect based on a combination of server-level, database-level, and user-specified options. Hence, manipulating these options needs to be done with care.

Further Reading

• SQL Server Stored Procedures and SET options [Link]
• SESSIONPROPERTY [MSDN Link]

Until we meet next time,
Be courteous. Drive responsibly.