Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT()

By:   |   Comments (5)   |   Related: > SQL Server 2016


Today, there are many reasons why you would want to maintain the equivalent of session variables within SQL Server. Perhaps you need to identify users (e.g. for auditing purposes) even if they all connect using web or middle-tier application and a common, SQL authentication login. Or you want to persist some information that can be accessible within a module that doesn't accept parameters, such as a trigger or view. Or you simply want to maintain session-scoped data that is costly to look up.

The current solution is to use CONTEXT_INFO(), which allows you to persist a binary value throughout the life of a session. There are four significant problems with CONTEXT_INFO():

  1. It is a single, binary value, and therefore cumbersome to work with; not just explicitly converting data to and from binary, but also the potential complication of storing multiple values in a single binary representation.
  2. It is limited to 128 bytes per connection. That is not a typo: 128 bytes, a significant limitation.
  3. The user has the ability to overwrite the data at any time. This can be a monumental security issue depending on how the information is being used - it can lead to escalation of privileges, SQL injection, and auditing fraud. I demonstrated this in SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting.
  4. It does not work quite the same in Azure SQL Database - if no value has been set, it returns an arbitrary GUID.

In SQL Server 2016, there is a new built-in function called SESSION_CONTEXT(), matching Oracle's equivalent. It overcomes the above limitations in the following ways:

  1. It supports key-value pairs - the keys are SYSNAME, while the values are SQL_VARIANT. SQL_VARIANT is admittedly not the easiest thing to work with, but it's a far cry better than bit-masking data into a single binary value.
  2. It supports 256 kilobytes - an improvement of 2,000X over CONTEXT_INFO().
  3. Any key can be marked as read only, so that a value cannot be changed once established.
  4. It will work identically in SQL Server 2016 and Azure SQL Database - if you ask for a key that has not been set, you will always receive NULL.

You set key-value pairs using the stored procedure sys.sp_set_session_context - this takes three arguments:

  • @key SYSNAME (required)
  • @value SQL_VARIANT (required, nullable)
  • @read_only BIT (optional, default 0)

You might be curious how the stored procedure actually works by digging into the source code, but there is no way to do this, even under the DAC connection. It is actually an extended procedure under the covers, even though it doesn't have the xp_ prefix:

EXEC sys.sp_helptext @objname = N'sys.sp_set_session_context';
-- returns (server internal)

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_set_session_context'))
-- returns NULL

SELECT [definition] 
  FROM sys.all_sql_modules
  WHERE [object_id] = OBJECT_ID(N'sys.sp_set_session_context');
-- returs 0 rows

You can't even look up the parameters to the procedure; this also returns 0 rows:

  FROM sys.all_parameters
  WHERE [object_id] = OBJECT_ID(N'sys.sp_set_session_context');

So let's try a few things through experimentation.

Setting a session variable

This shows the simple setting of a single session variable:

EXEC sys.sp_set_session_context @key = N'ID', @value = @ID;




Setting (and trying to update) a read-only value

We can set a key to be read-only by using the @read_only argument:

EXEC sys.sp_set_session_context @key = N'ID', @value = @ID, @read_only = 1;

Now, if we try to update it:

EXEC sys.sp_set_session_context @key = N'ID', @value = 400;

We get this error:

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 10
Cannot set key 'ID' in the session context. The key has been set as read_only for this session.

The only way to "change" a read-only value would be to manually disconnect, reconnect, and then re-establish the value from scratch (of course disconnecting would flush *all* session context values before returning the connection to the pool). Since a user could set any value they wanted upon connect/reconnect, it is expected that you would protect from this by setting any session variables you need to guard using @read_only within a logon trigger, your application's login routine, or some other mechanism either during the initial connection or at least before the user has an opportunity to send any ad hoc SQL to the server.

The importance of the N prefix

I've long been a proponent of properly prefixing Unicode string literals with the N prefix, especially when you don't know what they can contain. For the key name you pass to SESSION_CONTEXT(), you need to be explicit even if you know that the name could never contain non-ASCII:

EXEC sys.sp_set_session_context @key = N'foo', @value = 1;
SELECT SESSION_CONTEXT(N'foo'); -- this works
SELECT SESSION_CONTEXT('foo');  -- no N prefix

Resulting error:

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

Concatenating SQL_VARIANT output

Even if two session variables are equivalent strings, you can't concatenate them together. For example:

EXEC sys.sp_set_session_context @key = N'a', @value = N'blat';
EXEC sys.sp_set_session_context @key = N'b', @value = N'fung';
Msg 402, Level 16, State 1
The data types sql_variant and sql_variant are incompatible in the add operator.

If you try CONCAT:

EXEC sys.sp_set_session_context @key = N'a', @value = N'blat';
EXEC sys.sp_set_session_context @key = N'b', @value = N'fung';

You just get a different error message:

Msg 257, Level 16, State 3
Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

So, you have to put implicit CONVERT statements anywhere where you're going to use the output for anything other than direct display:

EXEC sys.sp_set_session_context @key = N'a', @value = N'blat';
EXEC sys.sp_set_session_context @key = N'b', @value = N'fung';



Overflowing the @key name

Since I suspect @key names could come from all over the place, I wanted to test a name that exceeded the limits of the SYSNAME datatype (128 Unicode characters):

DECLARE @SessionKey NVARCHAR(129) = REPLICATE(N'x', 129);
EXEC sys.sp_set_session_context @key = @SessionKey, @value = N'x';


Msg 15666, Level 16, State 1, Procedure sp_set_session_context, Line 1
Cannot set key 'xxx[...]xxx' in the session context. The size of the key cannot exceed 256 bytes.

You also can't fake it out by using a VARCHAR parameter instead. This yields the same error:

DECLARE @SessionKey VARCHAR(129) = REPLICATE('x', 129);
EXEC sys.sp_set_session_context @key = @SessionKey, @value = N'x';

Overflowing the SESSION_CONTEXT() buffer

I also wanted to see what would happen if I exceeded the 256kb limitation. So I wrote this code:

  @fill NCHAR(4000) = REPLICATE(N'X', 4000),
  @i TINYINT = 1,
  @SessionKey SYSNAME;

WHILE @i <= 40
  SET @SessionKey = N'Key' + CONVERT(VARCHAR(11),@i);
  EXEC sys.sp_set_session_context @key = @SessionKey, @value = @fill;
  SET @i += 1;

With this example, I was able to store 29 session values, before I got caught with this error:

Msg 15665, Level 16, State 1, Procedure sp_set_session_context, Line 1
The value was not set for key 'Key30' because the total size of keys and values in the session context would exceed the 256 kilobyte limit.

How many keys you will be able to use will fluctuate wildly depending on the data types of your values and the actual contents. That said, don't go out of your way to try to fill this buffer; I suspect in most cases you won't come anywhere near needing 256kb.

MARS support

There are two additional error messages I came across in sys.messages that lead me to believe MARS (Multiple Active Result Sets) will not be supported, even though it is supported with CONTEXT_INFO():

Msg 15667
Session context is not supported with MARS connections.

Msg 15668
Another batch in the session is using session context, new batch is not allowed to start.

In current builds, I could not generate these errors, but it is likely something that will kick in as we get closer to release, or just that I don't have an easy way to simulate a real set of MARS batches using just Management Studio.


SESSION_CONTEXT() provides the ability to maintain session variables within SQL Server, without many of the limitations inherent in CONTEXT_INFO(). This is another feature that should appear in all editions of SQL Server 2016 and, even as a developer productivity feature only (ignoring the security implication), will serve as another motivator for customers to upgrade. In reality, though, the real use case for this feature is Row-Level Security, and I will explore that in more detail in a future tip.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, November 15, 2018 - 12:18:59 PM - Aaron Bertrand Back To Top (78261)

Tim, I was doing some similar testing, and I hit a similar (but not identical) limit. Higher because I prefixed with 'x' instead of 'ID', and also smaller values, and it's the overall size that's capped. As I mentioned above, it's not so much about how many keys, but what's in them. They must have upped the limit from 256kb to 1MB at some point, but the docs still say 256kb. I submitted a pull request to have that changed.


Thursday, November 15, 2018 - 12:11:29 PM - Tim Cartwright Back To Top (78260)

 Aaron, looks like your article and the docs reflect an old size limit of 256kb. Looks like it might have been upped in a recent update. This is my @@version for the server I tested on.

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) 

Thursday, November 15, 2018 - 10:46:02 AM - Tim Cartwright Back To Top (78258)

Aaron, I wrote a test script to test it out myself.


@k NVARCHAR(20) = N''

WHILE @i < 9000 BEGIN


SET @k = 'ID' + CAST(@i AS NVARCHAR(20))

EXEC sys.sp_set_session_context @key = @k, @value = @k

SET @i += 1







Eventually it errors out with:

Msg 15665, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 0]

The value was not set for key 'ID2846' because the total size of keys and values in the session context would exceed the 1 MB limit.

So, it does not appear to have an actually key count limit, but a total size limit of 1mb. Which is a ton of data in comparison to CONTEXT_INFO.

Thursday, November 15, 2018 - 10:32:40 AM - Tim Cartwright Back To Top (78257)

Love the post and the new feature Aaron. Quick question, did you test to see if there is a limit to how many keys could be stored?

Thursday, November 19, 2015 - 2:54:51 PM - jeff_yao Back To Top (39105)

Thanks @Aaron for this tip, which actually I was interested in as well as I wrote about using context_info in my tip

When I saw sql 2016 CTP3 containing this new session_context feature, I immediately played with it, I think it is much better than context_info. However, there is still a big problem with session_context. The problem is:

How can I know what session_context are there with a given session_id?

For context_info, with session_id, we can find out a sessions's context_info via sys.dm_exec_requests or sys.dm_exec_sessions etc 

Most of the time, for a monitoring session to effectively manage existing sessions, we want to know what happens inside those monitored sessions, but currently, session_context is not exposed to outside yet.

I hope MS will solve this issue in RTM version. I put my note here so when other readers read this tip, they may understand the limitation of this new session_context feature.

get free sql tips
agree to terms