Using and maintaining session variables or data within a user session in SQL Server is not so straightforward. With the SET CONTEXT_INFO statement, you can set a 128-bytes long binary value and you can read it with the CONTEXT_INFO function. However, having one single value within the scope of the session is a huge limitation. SQL Server 2017 brings more functionality for playing with session scope-related data.

The SESSION_CONTEXT function returns the value of the specified key in the current session context. This value was previously set using the sys.sp_set_session_context procedure. It accepts the nvarchar data type as an input parameter. Interestingly, the function returns a value with the sql_variant data type.

Use the following code to set the value for the language key and then call the SESSION_CONTEXT function to read the value of the session key:

EXEC sys.sp_set_session_context @key = N'language', @value = N'German'; 
SELECT SESSION_CONTEXT(N'language'); 

The result of this action is shown as follows:

-------------
German

As mentioned earlier, the input data type must be nvarchar. An attempt to call the function with a different data type (including varchar and nchar!) results in an exception:

SELECT SESSION_CONTEXT('language'); 

You get the following message:

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

The function argument does not need to be a literal; you can put it in a variable, as shown in the following code example:

DECLARE @lng AS NVARCHAR(50) = N'language'; 
SELECT SESSION_CONTEXT(@lng); 

The size of the key cannot exceed 256 bytes and the limit for the total size of keys and values in the session context is 256 KB.

The system-stored procedure sys.sp_set_session_context and the function SESSION_CONTEXT allow you to create and maintain session variables within SQL Server and overcome limitations from previous SQL Server versions. The SESSION_CONTEXT function is used as a part of the Row-Level Security feature, and it will be explored in more detail in Chapter 8, Tightening Security.