{"id":4343,"date":"2015-12-15T00:56:26","date_gmt":"2015-12-14T19:26:26","guid":{"rendered":"http:\/\/sqlhints.com\/?p=4343"},"modified":"2015-12-31T23:23:31","modified_gmt":"2015-12-31T17:53:31","slug":"session_context-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2015\/12\/15\/session_context-in-sql-server-2016\/","title":{"rendered":"SESSION_CONTEXT in Sql Server 2016"},"content":{"rendered":"

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<\/strong> system stored procedure and these set values can be retrieved one at a time by using the SESSION_CONTEXT<\/strong> built in function.<\/p>\n

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.<\/p>\n

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

EXAMPLE 1<\/strong>: This example demonstrates how we can set the session context key named EmployeeId with it\u2019s value and retrieving this set keys value.<\/p>\n

\r\n--Set the session variable EmployeeId value\r\nEXEC sp_set_session_context 'EmployeeId', 5000\r\n--Retrieve the session variable EmployeeId value\r\nSELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId\r\n<\/pre>\n
RESULT:<\/strong>
\n\"SessionContext<\/a><\/p>\n
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:<\/p>\n
\r\n--Retrieve the session variable EmployeeId value\r\nSELECT SESSION_CONTEXT('EmployeeId') AS EmployeeId\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 8116, Level 16, State 1, Line 2
\nArgument data type varchar is invalid for argument 1 of session_context function.<\/p>\n
From the result it is clear that the SESSION_CONTEXT function always expects the key parameter passed to it should of type NVarchar.<\/p>\n
Let us re-execute the above statement by prefixing the key parameter by N<\/p>\n
\r\n--Retrieve the session variable EmployeeId value\r\nSELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId\r\n<\/pre>\n
RESULT:<\/strong>
\n\"SessionContext<\/a><\/p>\n
EXAMPLE 2:<\/strong> Below example shows how we can update the Session variable value<\/p>\n
\r\n--Set session variable EmployeeName value\r\nEXEC sp_set_session_context @key = 'EmployeeName', \r\n  @value='Basavaraj'\r\n--Retrieve the session variable EmployeeName value\r\nSELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName\r\n--Re-Set the EmployeeName session variable value\r\nEXEC sp_set_session_context @key ='EmployeeName',\r\n  @value='Basavaraj Biradar'\r\n--Retrieve the update session variable EmployeeName value\r\nSELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName\r\n<\/pre>\n
RESULT:<\/strong>
\n\"updating<\/a><\/p>\n
EXAMPLE 3:<\/strong> Below example demonstrates a session variable can be declared as readonly<\/p>\n
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\u2019t specify it or if we specify this parameter value as 0 then Sql Server will not allow to update this value later.<\/p>\n
\r\n--create a readonly session variable City\r\nEXEC sp_set_session_context @key = 'City', @value='Bangalore', \r\n  @read_only = 1\r\n--Retrieve the session variable City value\r\nSELECT SESSION_CONTEXT(N'City') City\r\n<\/pre>\n
RESULT:<\/strong>
\n\"SessionContext<\/a><\/p>\n
Now try to update the read-only session variable City value by the following statement<\/p>\n
\r\n--try to update the read-only City session variable value\r\nEXEC sp_set_session_context @key ='City',@value='Bengaluru'\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1
\nCannot set key ‘City’ in the session context. The key has been set as read_only for this session.<\/p>\n
SYNTAX of sp_set_session_context:<\/strong><\/p>\n
\r\nsp_set_session_context [ @key= ] 'key', [ @value= ] 'value'\r\n    [ , [ @read_only = ] { 0 | 1 } ]\r\n<\/pre>\n
Where: @key parameter is of type SYSNAME, @value is of type SQL_VARIANT and @read_only parameter is of type BIT<\/p>\n
SYNTAX of SESSION_CONTEXT<\/strong><\/p>\n
\r\nSESSION_CONTEXT(N'key')\r\n<\/pre>\n
Where:’key’ parameter to this function is of type SYSNAME and the return type of this function is SQL_VARIANT<\/p>\n
EXAMPLE 4:<\/strong> Below example demonstrates how a session variable can be accessed in stored procedure which is set somewhere outside.<\/p>\n
Let us create a stored procedure GetCountry by the following script:<\/p>\n
\r\nCREATE PROCEDURE GetCountry\r\nAS\r\nBEGIN\r\n\tSELECT SESSION_CONTEXT(N'Country') Country\r\nEND\r\n<\/pre>\n
Then by using the following script create the session variable Country<\/p>\n
\r\n--create a readonly session variable Country\r\nEXEC sp_set_session_context @key = 'Country', @value='India'\r\n--Retrieve the session variable Country value\r\nSELECT SESSION_CONTEXT(N'Country') Country\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Session<\/a><\/p>\n
Now in the same session execute the stored procedure GetCountry and see whether this SP has access to the Session variable Country:<\/p>\n
\r\nEXEC GetCountry\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Accessing<\/a><\/p>\n
EXAMPLE 5:<\/strong> Below example demonstrates the behavior when we try to fetch a non-existent session variable<\/p>\n
\r\nSELECT SESSION_CONTEXT(N'Continent') Continent\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Non<\/a><\/p>\n
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.<\/p>\n
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.<\/p>\n
We can check the overall memory usage for the session context across all the session by using the below statement:<\/p>\n
\r\nSELECT cache_address, name, pages_kb, pages_in_use_kb, \r\n\tentries_count, entries_in_use_count\r\nFROM sys.dm_os_memory_cache_counters \r\nWHERE TYPE = 'CACHESTORE_SESSION_CONTEXT'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"SessionContext<\/a><\/p>\n
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:<\/p>\n
\r\nDECLARE @Counter INT = 0,  @KeyName SYSNAME\r\nWHILE @Counter < 200\r\nBEGIN\r\n  SET @KeyName = N'Key' + CAST(@Counter AS NVARCHAR);\r\n  EXEC sys.sp_set_session_context @key = @KeyName, @value = 20\r\n  SET @Counter += 1;\r\nEND\r\n<\/pre>\n
[ALSO READ]<\/strong><\/p>\n