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

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan