Rajendra Gupta
Compare Stored procedure execution

SQL Server SESSION_CONTEXT() function with examples

March 9, 2020 by

This article explores the SQL Server session context function, SESSION_CONTEXT() and performs its comparison with the function, CONTEXT_INFO().

Introduction

Developers are familiar with the mechanism to store and retrieve session in a programming language such as ASP.Net. Before SQL Server 2016, we use the CONTEXT_INFO function. We retrieve session context values for all active sessions using this CONTEXT_INFO function.

  • CONTEXT_INFO returns a single binary value. We need to convert this binary value into a compatible data format
  • The binary value is limited to 128 bytes per connection

SQL Server session context

  • Users can overwrite value for it anytime. It might lead to security, audit-related issues
  • We cannot use this function in the Azure SQL database
  • Users with VIEW SERVER STATE permission and required SELECT permission can use DMV’s sys.dm_exec_sessions and sys.dm_exec_requests to retrieve context value for the session id

CONTEXT_INFO function

SQL Server 2016 introduced a new built-in-function SESSION_CONTEXT() as improvement over the existing CONTEXT_INFO function. It uses key-value pairs for storing the session data. It works with the SQL Azure database as well.

We set these key-value pairs using the stored procedure sp_set_session_context.

The Syntax of SQL Server session context function SESSION_CONTEXT()

The syntax of sp_set_session_context

It requires the following parameters.

  • @Key: It is the key that we set. It is of SYSNAME type and can be up to 256 bytes
  • @value: It is a value for the specified key. Its type is sql_variant. Its maximum size is 8,000 bytes
  • @read_only: We can specify value for this flag as 0,1
    • 1: We cannot change the key in the logical connection
    • 0: We can change the key value if read_only is set to zero

Let’s explore the SESSION_CONTEXT() using various examples.

Example 1 Use SESSION_CONTEXT() for a key retrieval

In this example, we configure a CustomerID key and set its value 101. Later, we use the SQL Server session context function SESSION_CONTEXT() and retrieve the key for it.

context function SESSION_CONTEXT()

Example 2: Update value for a key in the session

In this example, we update a key value in the same session. For updating value, we do not require any other stored procedure. We execute sp_set_session_context with a different value for an existing key.

Update value for a key in the session

In the output above, we see the updated value in the 2nd SESSION_CONTEXT() statement output.

Example 3: Update value for a read-only key in the session

System procedure sp_set_session_context accepts three parameters, as shown above in the syntax. We have not used the third parameter @read_only in the previous example. By default, SQL Server uses the default value 0 for the @read_only parameter. Let’s set it to 1 using the following query.

It sets the CustomerID key value to 101. Now, let’s try to update the key value. We get the message that it cannot set key value in the session context because it is set to read-only as shown in the following screenshot.

Update value for a read-only key in the session

Example 4: Check the output of SQL Server session context function SESSION_CONTEXT() without setting value for the key

In this example, we do not define any key using sp_set_session_context procedure. It means that the key is not valid. If we try to access the key-value using SESSION_CONTEXT(), it always returns a NULL value.

Example 5: Define a key without N prefix

In this example, we define a CutomerID key and set its value to 101

Let’s try to get the session context value without specifying the N prefix in the key name.

It returns an error message about invalid data type varchar().

Check the output of SQL Server session context function

It clearly shows that the SQL Server session context function SESSION_CONTEXT() function requires a NVARCHAR data type for its key.

Example 6: Use of SQL Server session context function SESSION_CONTEXT() in a stored procedure

In the previous example, we defined a key and its value directly in a session. In this example, we will use the session context in a stored procedure.

This procedure checks the session context for a key Mango. We require the output in a VARCHAR data type, so we use the SQL CONVERT function to change the data type from NVARCHAR to VARCHAR.

Execute this stored procedure and it returns NULL value in the output because we did not specify value for the key using the sp_set_session_context stored procedure.

Session context for stored procedure

Now, execute the stored procedure again after setting the key and its value.

We get the expected output, as shown below.

expected output

The key value is persistent throughout the session. We can execute the stored procedure multiple times, and it returns the same value in each execution.

If we execute this procedure in a different session, it returns NULL value because we did not set the key value for this session. Compare Stored procedure execution

These keys are independent in different sessions. For example, in the following screenshot, we have different values for the fruit key in session 1 and session 2.

We retrieve the session context in the particular session and get the key-value set in that session only. It does not override from the other session.

the key-value behaviour

Limitation of key and values pairs in the SQL Server session context

As highlighted at the beginning of this article, we have the following limitations for the key-value pairs.

  • Key can hold a maximum of 256 bytes (128 Unicode characters)
  • A value can hold up to 8000 bytes
  • The total size of a key-value pair in the security context cannot exceed beyond 1 MB

These limitations are in SQL Server 2019. We can have different values for it in SQL Server 2016 and 2017.

Let’s see these restrictions using examples. In the following query, we use a REPLICATE() function to replicate the value A 129 times in the @text variable.

DECLARE @text NVARCHAR(129)= REPLICATE(N’A’, 129);

We get the following error message because the size of the key exceeded 256 bytes.

Limitation of key and values pairs

Similarly, the total size of the key and value in a session cannot exceed 1 MB limit. The following code sets a few key and value until it is in 1 MB range. Once it crosses the limit, you get the highlighted error message shown below.

Limitation of key and values pairs for total size

Monitor session context memory usage for SQL Server session context

We can use the dynamic management view sys.dm_os_memory_cache_counters to monitor cache memory usage from all sessions.

Let’s close all sessions and specify a key, value using the following query.

Now, execute the DMV and check for the cache memory usage. The below screenshot shows currently we have a single key, and it is using 8 KB.

Monitor session context memory usage

  • pages_kb: It is the amount for the memory (in KB) allocated in the cache
  • entries_count: It is the number of entries in the cache

Now, execute the following query in a new query window for generating 10 keys and values using a WHILE loop.

Once we query the DMV, it shows memory usage from both sessions. The first session is using one key while another session is using 10 keys.

Monitor session context memory usage for multiple keys

Conclusion

In this article, we explored the session context function SESSION_CONTEXT() to manage the session variable in SQL Server. It is an enhancement over CONTEXT_INFO() and available from SQL Server 2016 onwards. We can also explore its use cases with Row-level security feature in SQL Server.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views