In .Net we have Session object which provides a mechanism to store and retrieve values for a user as user navigates ASP.NET pages in a Web application for that session. With Sql Server 2016 we are getting the similar feature in Sql Server, where we can store multiple key and value pairs which are accessible throughout that session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the SESSION_CONTEXT built in function.

This article explains in detail with extensive list of examples on how we can store the information that can be accessed anywhere in that session.

In this article for the Session Context key value pairs I will be interchangeably referring them as Session Variables. I hope most of the .Net developers too prefer calling them as session variables.

EXAMPLE 1: This example demonstrates how we can set the session context key named EmployeeId with it’s value and retrieving this set keys value.

--Set the session variable EmployeeId value
EXEC sp_set_session_context 'EmployeeId', 5000
--Retrieve the session variable EmployeeId value
SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

RESULT:

Let us try to re-get the session context key EmployeeId value, but this time while specifying the key EmployeeId parameter skipping the prefix N:

--Retrieve the session variable EmployeeId value
SELECT SESSION_CONTEXT('EmployeeId') AS EmployeeId

RESULT:

Msg 8116, Level 16, State 1, Line 2
Argument data type varchar is invalid for argument 1 of session_context function.

From the result it is clear that the SESSION_CONTEXT function always expects the key parameter passed to it should of type NVarchar.

Let us re-execute the above statement by prefixing the key parameter by N

--Retrieve the session variable EmployeeId value
SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

RESULT:

EXAMPLE 2: Below example shows how we can update the Session variable value

--Set session variable EmployeeName value
EXEC sp_set_session_context @key = 'EmployeeName', 
  @value='Basavaraj'
--Retrieve the session variable EmployeeName value
SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName
--Re-Set the EmployeeName session variable value
EXEC sp_set_session_context @key ='EmployeeName',
  @value='Basavaraj Biradar'
--Retrieve the update session variable EmployeeName value
SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

RESULT:

EXAMPLE 3: Below example demonstrates a session variable can be declared as readonly

First create the readonly session variable City by using the following statement. We can mark a session variable as readonly by specying the parameter @read_only value as 1. This parameter is optional, if we don’t specify it or if we specify this parameter value as 0 then Sql Server will not allow to update this value later.

--create a readonly session variable City
EXEC sp_set_session_context @key = 'City', @value='Bangalore', 
  @read_only = 1
--Retrieve the session variable City value
SELECT SESSION_CONTEXT(N'City') City

RESULT:

Now try to update the read-only session variable City value by the following statement

--try to update the read-only City session variable value
EXEC sp_set_session_context @key ='City',@value='Bengaluru'

RESULT:

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1
Cannot set key ‘City’ in the session context. The key has been set as read_only for this session.

SYNTAX of sp_set_session_context:

sp_set_session_context [ @key= ] 'key', [ @value= ] 'value'
    [ , [ @read_only = ] { 0 | 1 } ]

Where: @key parameter is of type SYSNAME, @value is of type SQL_VARIANT and @read_only parameter is of type BIT

SYNTAX of SESSION_CONTEXT

SESSION_CONTEXT(N'key')

Where:’key’ parameter to this function is of type SYSNAME and the return type of this function is SQL_VARIANT

EXAMPLE 4: Below example demonstrates how a session variable can be accessed in stored procedure which is set somewhere outside.

Let us create a stored procedure GetCountry by the following script:

CREATE PROCEDURE GetCountry
AS
BEGIN
	SELECT SESSION_CONTEXT(N'Country') Country
END

Then by using the following script create the session variable Country

--create a readonly session variable Country
EXEC sp_set_session_context @key = 'Country', @value='India'
--Retrieve the session variable Country value
SELECT SESSION_CONTEXT(N'Country') Country

RESULT:

Now in the same session execute the stored procedure GetCountry and see whether this SP has access to the Session variable Country:

EXEC GetCountry

RESULT:

EXAMPLE 5: Below example demonstrates the behavior when we try to fetch a non-existent session variable

SELECT SESSION_CONTEXT(N'Continent') Continent

RESULT:

From the result it is clear that, NULL value will be returned by SESSION_CONTEXT function if we try to fetch non-existent session variable.

The maximum size of the session context is limited to 256 kb. So there is no specific limit or the number of key and value pairs that can be stored in the Session Context as long as the total size is less than 256 Kb and it raises an error if the total size cross this max size limit. Note this limit of 256 Kb is at session level, so we can have multiple sessions at the same time with each session max limit is 256 kb.

We can check the overall memory usage for the session context across all the session by using the below statement:

SELECT cache_address, name, pages_kb, pages_in_use_kb, 
	entries_count, entries_in_use_count
FROM sys.dm_os_memory_cache_counters 
WHERE TYPE = 'CACHESTORE_SESSION_CONTEXT'

RESULT:

From the above result we can see that we have two active sql sessions with session context info stored. Out of these two session, the 2nd session is having 200 key value pairs stored in it and it is using 88 kb. And below is the script which is used to generate the 200 key value pairs:

DECLARE @Counter INT = 0,  @KeyName SYSNAME
WHILE @Counter < 200
BEGIN
  SET @KeyName = N'Key' + CAST(@Counter AS NVARCHAR);
  EXEC sys.sp_set_session_context @key = @KeyName, @value = 20
  SET @Counter += 1;
END

[ALSO READ]

• Native JSON Support in Sql Server 2016
  • FOR JSON Clause in Sql Server 2016
  • OPENJSON Function in Sql Server 2016
  • ISJSON Function in Sql Server 2016
  • JSON_VALUE Function in Sql Server 2016
  • JSON_QUERY Function in Sql Server 2016
  • lax and strict JSON Path modes in Sql Server 2016
  • Indexing Strategy for JSON Value in Sql Server 2016
• DROP IF EXISTS Statement in Sql Server 2016
• Compare Execution Plans in Sql Server 2016
• Live Query Statistics in Sql Server 2016
• DATEDIFF_BIG Function in Sql Server 2016
• Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
• SESSION_CONTEXT in Sql Server 2016