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
|
1 2 |
SET CONTEXT_INFO 0x123456 select CONTEXT_INFO() |
- 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
|
1 2 3 4 5 |
Select CONTEXT_INFO as info from sys.dm_exec_sessions where session_id=63 Select CONTEXT_INFO as info from sys.dm_exec_requests where session_id=63 |
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()
|
1 |
SESSION_CONTEXT(N'key') |
The syntax of sp_set_session_context
|
1 2 |
sp_set_session_context [ @key= ] N'key', [ @value= ] 'value' , [@read_only = ] { 0 | 1 } ] |
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.
|
1 2 |
EXEC sp_set_session_context 'CustomerID', 101 SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID |
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.
|
1 2 3 4 5 6 7 |
EXEC sp_set_session_context 'CustomerID', 101 SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID EXEC sp_set_session_context 'CustomerID', 999 SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID |
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.
|
1 2 3 |
EXEC sp_set_session_context @key='CustomerID', @value=101, @read_only=1 SELECT SESSION_CONTEXT(N'CustomerID') AS CustomerID |
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.
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.
|
1 2 3 |
USE [master] GO SELECT SESSION_CONTEXT(N'MySystem') |
Example 5: Define a key without N prefix
In this example, we define a CutomerID key and set its value to 101
|
1 |
EXEC sp_set_session_context @key='CustomerID', @value=101, @read_only=1 |
Let’s try to get the session context value without specifying the N prefix in the key name.
|
1 |
SELECT SESSION_CONTEXT('CustomerID') AS CustomerID |
It returns an error message about invalid data type varchar().
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.
|
1 2 3 4 5 |
CREATE PROCEDURE [CheckSecurityContext] AS BEGIN DECLARE @Fruit varchar(20) = CONVERT(varchar(20), SESSION_CONTEXT(N'FRUIT')) SELECT CheckSecurityContext = @Fruit END |
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.
Now, execute the stored procedure again after setting the key and its value.
|
1 2 3 |
EXEC sp_set_session_context @key = N'Fruit', @value = N'Mango' EXEC CheckSecurityContexts |
We get the expected output, as shown below.
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.
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.
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);
|
1 2 3 4 |
DECLARE @text NVARCHAR(129)= REPLICATE(N'A', 129); EXEC sys.sp_set_session_context @key = @text, @value = N'A'; |
We get the following error message because the size of the key exceeded 256 bytes.
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.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @Replicate NVARCHAR(4000)= REPLICATE(N'X', 4000), @i INT= 1, @Key SYSNAME; WHILE @i <= 60 BEGIN SET @Key = N'Iteraction' + CONVERT(VARCHAR(20), @i); EXEC sys.sp_set_session_context @key = @Key, @value = @Replicate; SET @i+=1; END; |
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.
|
1 2 3 |
SELECT * FROM sys.dm_os_memory_cache_counters WHERE TYPE = 'CACHESTORE_SESSION_CONTEXT'; |
Let’s close all sessions and specify a key, value using the following query.
|
1 2 3 |
EXEC sp_set_session_context @key = N'Fruit', @value = N'Mango' Select SESSION_CONTEXT(N'Fruit') |
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.
- 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.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @Replicate NVARCHAR(4000)= REPLICATE(N'X', 4000), @i INT= 1, @Key SYSNAME; WHILE @i <= 10 BEGIN SET @Key = N'Iteraction' + CONVERT(VARCHAR(20), @i); EXEC sys.sp_set_session_context @key = @Key, @value = @Replicate; SET @i+=1; END; |
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023