By: Aaron Bertrand | Updated: 2015-11-19 | Comments (5) | Related: More > 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():
- 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.
- It is limited to 128 bytes per connection. That is not a typo: 128 bytes, a significant limitation.
- 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.
- 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:
- 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.
- It supports 256 kilobytes - an improvement of 2,000X over CONTEXT_INFO().
- Any key can be marked as read only, so that a value cannot be changed once established.
- 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
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');
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:
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 GO SELECT SESSION_CONTEXT('foo'); -- no N prefix
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'; SELECT SESSION_CONTEXT(N'a') + SESSION_CONTEXT(N'b');
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'; SELECT CONCAT(SESSION_CONTEXT(N'a'), SESSION_CONTEXT(N'b'));
You just get a different error message:
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'; SELECT CONCAT( CONVERT(NVARCHAR(4000),SESSION_CONTEXT(N'a')), CONVERT(NVARCHAR(4000),SESSION_CONTEXT(N'b')) );
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';
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:
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:
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.
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():
Session context is not supported with MARS connections.
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.
- 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:
Last Updated: 2015-11-19
About the author
View all my tips