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:
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
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.
Until we meet next time,
Be courteous. Drive responsibly.