SQL SESSIONPROPERTY()

In SQL Server, the SESSIONPROPERTY function is used to retrieve information about the current session settings. It helps developers and database administrators check the status of specific session properties, such as transaction isolation level, ANSI settings, and connection attributes.

In this tutorial, we will explore the SESSIONPROPERTY function, its syntax, and practical examples demonstrating its use.

Syntax of SQL SESSIONPROPERTY

The SESSIONPROPERTY function takes a property name as an argument and returns the corresponding session setting value.

</>
Copy
SELECT SESSIONPROPERTY('property_name') AS property_value;

Here, property_name is the name of the session property you want to retrieve. The function returns an integer value representing the status of the specified property.

Commonly Used SESSIONPROPERTY Values

Property NameDescriptionPossible Values
ANSI_NULLSIndicates if NULL comparisons follow ANSI SQL standards.1 (ON) or 0 (OFF)
ANSI_WARNINGSDetermines if warnings are issued for divide-by-zero and overflow errors.1 (ON) or 0 (OFF)
ARITHABORTSpecifies whether a query is terminated on arithmetic errors.1 (ON) or 0 (OFF)
CONCAT_NULL_YIELDS_NULLControls whether concatenating NULL with a string results in NULL.1 (ON) or 0 (OFF)
TRANSACTION ISOLATION LEVELReturns the transaction isolation level for the session.0 (Read Uncommitted), 1 (Read Committed), 2 (Repeatable Read), 3 (Serializable), 4 (Snapshot)

Examples of SQL SESSIONPROPERTY

Let’s explore how to use the SESSIONPROPERTY function with examples.

1 Checking the ANSI_NULLS Setting for a User Session

Suppose Arjun is running a query and wants to check whether the ANSI_NULLS setting is enabled in his session.

</>
Copy
SELECT SESSIONPROPERTY('ANSI_NULLS') AS ansi_nulls_setting;

Explanation:

  • If the result is 1, ANSI_NULLS is enabled, meaning NULL comparisons follow ANSI SQL standards.
  • If the result is 0, ANSI_NULLS is disabled, meaning NULL comparisons do not follow ANSI SQL rules.

Example Output:

This indicates that ANSI_NULLS is enabled for Arjun’s session.

Conclusion

The SESSIONPROPERTY function in SQL Server is a used for retrieving session-specific settings. In this tutorial, we learned how to check:

  1. ANSI SQL settings for the session.
  2. Various connection properties related to performance and query execution.