SQL Server SESSIONPROPERTY

There are various settings that can be done at the session level. For example, you can set ANSI_NULLS OFF for a session and ON for another session. If you want to know if a setting is ON or OFF before using the setting, you can make use of system function SESSIONPROPERTY.

SESSIONPROPERTY will return 1 or 0 depending on whether the SET OPTION is ON or OFF

Consider the following example

SELECT SESSIONPROPERTY('ANSI_NULLS')

If the above statement returns 1, then ANSI_NULLS is ON in the session, otherwise it is OFF
Similarly the same function can be used to know the SET OPTION for various other options specified below

QUOTED_IDENTIFIER
ARITHABORT
ANSI_NULL_DFLT_ON
ANSI_WARNINGS
ANSI_PADDING
CONCAT_NULL_YIELDS_NULL

Eg:

SELECT SESSIONPROPERTY('ANSI_WARNINGS')



If the above returns 1, then ANSI_WARNINGS is ON in the session, otherwise it is OFF.

So when you are working in SQL Server, you can check the status of these settings using the SESSIONPROPERTY function