SQL Server: SESSIONPROPERTY Function

In this post explains how to use the SESSIONPROPERTY function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the SESSIONPROPERTY function returns the setting for a specified option of a session.

Syntax

The syntax for the SESSIONPROPERTY function in SQL Server (Transact-SQL) is:

SESSIONPROPERTY( option )

Parameters or Arguments

option

The option that you wish to retrieve the session settings for. It can be one of the following values.

Option	Explanation
ANSI_NULLS	SQL-92 compliant behavior of NULL values 1=ON, 0=OFF
ANSI_PADDING	SQL-92 compliant behavior of padding and trailing blanks in the storage of character and binary columns 1=ON, 0=OFF
ANSI_WARNINGS	SQL-92 compliant behavior of warnings and error messages 1=ON, 0=OFF
ARITHABORT	Abort queries when an overflow or divide-by-error occurs 1=ON, 0=OFF
CONCAT_NULL_YIELDS_NULL	Concatenated results (that are NULL) are treated as NULL rather than empty strings 1=ON, 0=OFF
NUMERIC_ROUNDABORT	Warnings and error messages are raised when rounding causes a loss of precision 1=ON, 0=OFF
QUOTED_IDENTIFIER	SQL-92 compliant behavior of quotation marks to delimit literal strings and identifiers 1=ON, 0=OFF

Applies To

The SESSIONPROPERTY function can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server SESSIONPROPERTY function examples and explore how to use the SESSIONPROPERTY function in SQL Server (Transact-SQL).

For example:

SELECT SESSIONPROPERTY('ANSI_NULLS');
Output: 1

SELECT SESSIONPROPERTY('ANSI_PADDING');
Output: 1

SELECT SESSIONPROPERTY('ANSI_WARNINGS');
Output: 1

SELECT SESSIONPROPERTY('ARITHABORT');
Output: 1

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL');
Output: 1

SELECT SESSIONPROPERTY('NUMERIC_ROUNDABORT');
Output: 1

SELECT SESSIONPROPERTY('QUOTED_IDENTIFIER');
Output: 1