SQL Server SESSIONPROPERTY() Function

SQL Server SESSIONPROPERTY() Function

The SQL Server SESSIONPROPERTY() function returns information about the session settings. This function is useful for retrieving specific properties of the current session, such as the language, isolation level, or the status of ANSI settings.

Syntax

SELECT SESSIONPROPERTY(property_name);

The SESSIONPROPERTY() function takes a single argument:

  • property_name: The name of the session property to retrieve. Common values include 'ANSI_NULLS', 'ANSI_PADDING', 'ANSI_WARNINGS', 'ARITHABORT', 'CONCAT_NULL_YIELDS_NULL', 'DATEFIRST', 'DATEFORMAT', 'LANGID', 'LANGUAGE', 'LOCK_TIMEOUT', 'NUMERIC_ROUNDABORT', 'QUOTED_IDENTIFIER', 'TEXTSIZE', and 'TRANSACTION ISOLATION LEVEL'.

Example SQL Server SESSIONPROPERTY() Function Queries

Let's look at some examples of SQL Server SESSIONPROPERTY() function queries:

1. Checking ANSI_NULLS Setting

SELECT SESSIONPROPERTY('ANSI_NULLS') AS ansi_nulls_setting;

This query returns the status of the 'ANSI_NULLS' setting for the current session. The result will be:

ansi_nulls_setting
------------------
1

2. Checking Language Setting

SELECT SESSIONPROPERTY('LANGUAGE') AS language_setting;

This query returns the language setting for the current session. The result will be:

language_setting
----------------
us_english

3. Checking Isolation Level

SELECT SESSIONPROPERTY('TRANSACTION ISOLATION LEVEL') AS isolation_level;

This query returns the transaction isolation level for the current session. The result will be:

isolation_level
---------------
2

Note: The isolation level value is returned as an integer, where 0 = Unspecified, 1 = Read Uncommitted, 2 = Read Committed, 3 = Repeatable Read, 4 = Serializable, and 5 = Snapshot.

4. Checking ANSI_WARNINGS Setting

SELECT SESSIONPROPERTY('ANSI_WARNINGS') AS ansi_warnings_setting;

This query returns the status of the 'ANSI_WARNINGS' setting for the current session. The result will be:

ansi_warnings_setting
----------------------
1

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SESSIONPROPERTY() function to check various session settings.

Step 1: Creating a Table

This step involves creating a new table named session_info to store some sample data about session properties.

CREATE TABLE session_info (
    id INT PRIMARY KEY,
    property_name VARCHAR(50),
    property_value VARCHAR(50)
);

In this example, we create a table named session_info with columns for id, property_name, and property_value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the session_info table using the SESSIONPROPERTY() function to retrieve the values.

INSERT INTO session_info (id, property_name, property_value)
VALUES (1, 'ANSI_NULLS', CAST(SESSIONPROPERTY('ANSI_NULLS') AS VARCHAR(50)));
INSERT INTO session_info (id, property_name, property_value)
VALUES (2, 'LANGUAGE', CAST(SESSIONPROPERTY('LANGUAGE') AS VARCHAR(50)));
INSERT INTO session_info (id, property_name, property_value)
VALUES (3, 'TRANSACTION ISOLATION LEVEL', CAST(SESSIONPROPERTY('TRANSACTION ISOLATION LEVEL') AS VARCHAR(50)));
INSERT INTO session_info (id, property_name, property_value)
VALUES (4, 'ANSI_WARNINGS', CAST(SESSIONPROPERTY('ANSI_WARNINGS') AS VARCHAR(50)));

Here, we insert data into the session_info table.

Step 3: Retrieving Session Properties

This step involves selecting the data from the session_info table to view the session properties.

SELECT * FROM session_info;

This query retrieves all the rows from the session_info table. The result will be:

id  property_name                 property_value
--- ----------------------------  --------------
1   ANSI_NULLS                    1
2   LANGUAGE                      us_english
3   TRANSACTION ISOLATION LEVEL   2
4   ANSI_WARNINGS                 1

Conclusion

The SQL Server SESSIONPROPERTY() function is a powerful tool for retrieving information about the current session's settings. Understanding how to use the SESSIONPROPERTY() function and its syntax is essential for effective session management and troubleshooting in SQL Server.