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

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

Problem

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.

Solution

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:

SELECT * 
  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:

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

SELECT SESSION_CONTEXT(N'ID');

Result:

255

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

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

DECLARE @ID INT = 255;
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:

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
GO
SELECT SESSION_CONTEXT('foo');  -- no N prefix

Resulting error:

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';
SELECT SESSION_CONTEXT(N'a') + SESSION_CONTEXT(N'b');

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';
SELECT CONCAT(SESSION_CONTEXT(N'a'), SESSION_CONTEXT(N'b'));

You just get a different error message:

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';
SELECT CONCAT(
  CONVERT(NVARCHAR(4000),SESSION_CONTEXT(N'a')), 
  CONVERT(NVARCHAR(4000),SESSION_CONTEXT(N'b'))
);

Result:

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';

Error:

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:

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

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

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

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():

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.

Summary

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

• Download SQL Server 2016 CTP3 (or register for a trial of Azure SQL Database, where this feature is also available).
• Try out SESSION_CONTEXT() in scenarios where it may seem useful.
• See these related tips and other resources:
• SQL Server 2016 Row Level Security Introduction
• SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting
• sql_variant (MSDN)
• CONTEXT_INFO (MSDN)
• SET CONTEXT_INFO (MSDN)
• Using Session Context Information (TechNet)
• Exploiting Context_Info for Fun and Audit

About the author

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, SQLPerformance.com, and also blogs at sqlblog.org.

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

View all my tips