SQL Server SESSIONPROPERTY() Function

The SESSIONPROPERTY() function in SQL Server retrieves the current setting of a specific session option. Session options control various aspects of how a SQL Server session behaves.

SESSIONPROPERTY(): Definition and Usage

This function is useful for querying the current state of your SQL Server session, particularly for debugging, troubleshooting, or dynamically adapting your code based on session settings. It's especially handy when you need to know if certain features are enabled or disabled during your database operations.

Syntax

Syntax

SESSIONPROPERTY(option)

Parameter Values

Parameter Description
option The session option whose setting you want to retrieve. Options include:
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • NUMERIC_ROUNDABORT
  • QUOTED_IDENTIFIER
This is required.

Example

Checking ANSI_NULLS Setting

This example shows how to check the current setting of the ANSI_NULLS session option. This option controls how SQL Server handles NULL values in comparisons.

Syntax

SELECT SESSIONPROPERTY('ANSI_NULLS');
Output

1 (if ANSI_NULLS is ON), 0 (if ANSI_NULLS is OFF)