Session Variable – Context_Info -SQL-Server 2005

Session Variable – Context_Info:

Session is a powerful tool in any of the programming language. SQL-Server is not a full fledge programming language but it do supports session variable for current session or connection. It stores value of session in 128 byte of binary information.

You can set or retrieve its value from CONTEXT_INFO column of the following system views.

sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

While storing the value in CONTEXT_INFO is aware that you are having 128 bit, whatever you will assign, will be stored in that 128 bit space only. Let’s have a look at it.

I am going to store two VARCHAR variable in CONTEXT_INFO. First variable will cost 6 bit and another variable cost 4 bit. If the datatype you use, is not able to implicitly convert itself to binary than you will have to do that task manually. Conversion table of datatype is given at the end of article.

Now let’s have a look at example.

USE AdventureWorks

DECLARE @Fname VARCHAR(10), @Lname VARCHAR(10), @bVar binary(128)

SET @Fname='Ritesh'

SET @Lname='Shah'

SELECT @bVar=CONVERT(binary(6),@Fname)+CONVERT(binary(4),@Lname)

SET CONTEXT_INFO @bVar

GO

We have declare two VARCHAR variable and one binary variable. Set the values in both VARCHAR variable, convert it to binary and stored it in binary variable. Finally set the binary variable to CONTEXT_INFO column. You can query system views to get value of CONTEXT_INFO like below.

SELECT Fname = convert(varchar(10), substring(context_info, 1, 6))

FROM master..sysprocesses

WHERE spid = @@spid

SELECT Lname = convert(varchar(10), substring(context_info, 7, 4))

FROM master..sysprocesses

WHERE spid = @@spid

@@spid is used to get the exact session ID. As I explained previously also, you can get the CONTEXT_INFO column in three system views so that you can query any of the views like:

SELECT context_info FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

SELECT context_info FROM sys.dm_exec_requests WHERE session_id = @@SPID;

SELECT context_info FROM sys.sysprocesses WHERE spid = @@SPID;

Below is the conversion table I got from the SQL Server documentation.