There are many application which have requirement to store and retrieve session context information from SQL Server. Session context information is very useful in cases which can be used to store information specific to each user or their current state of the application. This is required in some applications to control the logic in Transact-SQL statements. Prior to SQL Server 2016, application developers used to rely mostly on CONTEXT_INFO function or sometimes on TSQL session variables, but both these have their own disadvantages like, Transact-SQL variables, scope is limited to that current Transact-SQL batch, stored procedure, trigger, or user-defined function. SQL/Application developers were using the CONTEXT_INFO function for retrieving the session context for the current session. Also, CONTEXT_INFO allows to retrieve session context values for all current sessions and batches from the context_info columns in the sys.dm_exec_requests or sys.dm_exec_sessions dynamic management views. Inorder to run queries against these views, required SELECT and VIEW SERVER STATEE permissions on the SQL Server instance, but these permissions were not required anymore when we use the CONTEXT_INFO function.
But this CONTEXT_INFO function has its own limitations too like, It is a single and a binary value, therefore is very difficult to work with because we will need to convert the data from binary to human readable format and also the involves the complexity of storing multiple values in a single binary representation, which will be difficult to use, another limitation include the size supported was only 128 bytes for the connection which was not liked my many developers, this CONTEXT_INFO also has another security concern too as it allows the users to overwrite the data at any time, and also this does not work well with SQL Azure databases. So, upon the feedback from the developer community, Microsoft has introduced new built-in function called SESSION_CONTEXT() starting with Microsoft SQL Server 2016. This is not a new innovation by Microsoft, but there are equivalents in other RDBMS like Oracle.
The way SESSION_CONTEXT function is different from CONTEXT_INFO is that the SESSION_CONTEXT uses key-value pairs to store the session data and the keys are of SYSNAMEE type where as the values are SQL_VARIANT type. Now, the size of the data that can be store has been increased/set to 256 KB, which is significantly greater than the size of data allowed with CONTEXT_INFO. Also, the security concerns has been addressed, wherein we can set the key as read only, so the value will not be able to be changed once it has been established. Also, as this has been introduced after SQL Azure was launched, Microsoft has kept the SQL Azure databases in mind and made this work similarly on both on-premise and on SQL Azure databases. We need to set the key-value pairs using the stored procedure called as sys.sp_set_session_context.
@key – is the key which is being set, and is of type sysnamee. The maximum key size allowed 128 bytes.
@value – is the value for the specified key, and it is of type sql_variantt. Setting this value of NULL will free the memory. The maximum size is 8,000 bytes.
@read_only – This allows zero or one. This is a flag of type bit. If 1 is set, then the value for the specified key cannot be changed again on this logical connection. If 0 (default), then the value can be changed.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings