Blog

SQL Server: SESSIONPROPERTY Function

SESSIONPROPERTY function 

This SQL Server tutorial 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.

OptionExplanation
ANSI_NULLSSQL-92 compliant behavior of NULL values
1=ON, 0=OFF
ANSI_PADDINGSQL-92 compliant behavior of padding and trailing blanks in the storage of character and binary columns
1=ON, 0=OFF
ANSI_WARNINGSSQL-92 compliant behavior of warnings and error messages
1=ON, 0=OFF
ARITHABORTAbort queries when an overflow or divide-by-error occurs
1=ON, 0=OFF
CONCAT_NULL_YIELDS_NULLConcatenated results (that are NULL) are treated as NULL rather than empty strings
1=ON, 0=OFF
NUMERIC_ROUNDABORTWarnings and error messages are raised when rounding causes a loss of precision
1=ON, 0=OFF
QUOTED_IDENTIFIERSQL-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 functions examples and explore how to use the SESSIONPROPERTY function in SQL Server (Transact-SQL).

For example:

SELECT SESSIONPROPERTY('ANSI_NULLS');
Result: 1

SELECT SESSIONPROPERTY('ANSI_PADDING');
Result: 1

SELECT SESSIONPROPERTY('ANSI_WARNINGS');
Result: 1

SELECT SESSIONPROPERTY('ARITHABORT');
Result: 1

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL');
Result: 1

SELECT SESSIONPROPERTY('NUMERIC_ROUNDABORT');
Result: 1

SELECT SESSIONPROPERTY('QUOTED_IDENTIFIER');
Result: 1

This Post Has 2 Comments

Leave a Reply