Context Information is a binary value of size up to 128 bytes that can be set for a session and referenced in multiple batches, stored procedures, triggers, or user-defined functions executing on the same session.
There are two commands that are used to set and get the context information.
SET CONTEXT_INFO – It helps to assign session context value
CONTEXT_INFO () – It helps to retrieve session context value.
We can also use DMV to see the context binary value in the current session. These are the DMVs that can use to view the context information.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
SET CONTEXT_INFO
It sets the binary information to the session context of the current session or connection.
Syntax
SET CONTEXT_INFO binary_string_constant
or
SET CONTEXT_INFO @binary_variable
Example
In this example I am going to generate a binary value for a string constant and set that value to the session context.
To Generate binary value, we can use cast or convert functions. In this example, I used this statement to create the binary value of a string value.
SELECT CAST('SQL Server Rider' AS VARBINARY(50))
SET CONTEXT_INFO 0x53514C20536572766572205269646572
CONTEXT_INFO ()
This function is used to retrieve the session context value that is already set by user. Otherwise, it will return a null value. It returns 128 bytes binary value. We can use data type conversion function to convert the binary value to other type.
Syntax
CONTEXT_INFO ()
Example
In this example I am going to use the context_info() function to read the context data and convert into string value.
SET CONTEXT_INFO 0x53514C20536572766572205269646572 SELECT CAST(CONTEXT_INFO() AS varchar(100))
Output Section
This section contains the result of all the SQL query used in the examples.
Using DMV to get the context information
SQL Query
SET CONTEXT_INFO 0x53514C20536572766572205269646572 SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID
Output
SQL Server Rider 