Sharing State in SQL Server 2016 with SESSION_CONTEXT

If you’ve ever wanted to share session state across all stored procedures and batches throughout the lifetime of a database connection, you’re going to love SESSION_CONTEXT. When you connect to SQL Server 2016, you get a stateful dictionary, or what’s often referred to as a state bag, some place where you can store values, like strings and numbers, and then retrieve it by a key that you assign. In the case of SESSION_CONTEXT, the key is any string, and the value is a sql_variant, meaning it can accommodate a variety of types.

Once you store something in SESSION_CONTEXT, it stays there until the connection closes. It is not stored in any table in the database, it just lives in memory as long as the connection remains alive. And any and all T-SQL code that’s running inside stored procedures, triggers, functions, or whatever, can share whatever you shove into SESSION_CONTEXT.

The closest thing like this we’ve had until now has been CONTEXT_INFO, which allows you to store and share a single binary value up to 128 bytes long, which is far less flexible than the dictionary you get with SESSION_CONTEXT, which supports multiple values of different data types.

SESSION_CONTEXT is easy to use, just call sp_set_session_context to store the value by a desired key. When you do that, you supply the key and value of course, but you can also set the read_only parameter to true. This is locks the value in session context, so that it can’t be changed for the rest of the lifetime of the connection. So, for example, it’s easy for a client application to call this stored procedure to set some session context values right after it establishes the database connection. If the application sets the read_only parameter when it does this, then the stored procedures and other T-SQL code that then executes on the server can only read the value, they can’t change what was set by the application running on the client.

How do you extract a value out of session context? Well, by using the SESSION_CONTEXT function of course. You supply the key, and the function returns the value. But remember, it returns this as a sql_variant, so you’ll usually need to cast or convert the value into whatever data type you need, like a varchar, int, or date.

Let’s demonstrate with a quick example.

First I’ll create this stored procedure that does something. And to do it, the stored procedure needs to know the region, but it doesn’t get the region using a parameter. Instead, it calls the new SESSION_CONTEXT function, requesting the value keyed as UsRegion, which we cast to a varchar(20) using CONVERT.

CREATE PROCEDURE DoThis AS
BEGIN
	DECLARE @UsRegion varchar(20) = CONVERT(varchar(20), SESSION_CONTEXT(N'UsRegion'))
	SELECT DoThis = @UsRegion
END

And here’s another stored procedure that also takes no parameters, and gets the region from session_context.

CREATE PROCEDURE DoThat AS
BEGIN
	DECLARE @UsRegion varchar(20)
	SET @UsRegion = CONVERT(varchar(20), SESSION_CONTEXT(N'UsRegion'))
	SELECT DoThat = @UsRegion
END

Both these procedures are expecting some earlier code to store the desired region into session context. Until that happens, SESSION_CONTEXT simply returns NULL.

EXEC DoThis
EXEC DoThat

DoThis
--------------------
NULL

DoThat
--------------------
NULL

So Let’s call sp_set_session_context, and set the region to Southwest.

EXEC sp_set_session_context @key = N'UsRegion', @value = N'Southwest'

Now when we call the procedures, they both get Southwest from the session context.

EXEC DoThis
EXEC DoThat

DoThis
--------------------
Southwest

DoThat
--------------------
Southwest

Now before moving on to change the value, let’s first see that the value persists only for the lifetime of the connection. Run it a few more times, and you can see it’s still returning Southwest, but then close the connection and open a new one. Now running the stored procs again, the region is NULL, because session context is empty.

EXEC DoThis
EXEC DoThat

DoThis
--------------------
NULL

DoThat
--------------------
NULL

Now call sp_set_session_context again to change the region to Northeast,

EXEC sp_set_session_context @key = N'UsRegion', @value = N'Northeast'

And the procedures show Northeast, just as expected:

EXEC DoThis
EXEC DoThat

DoThis
--------------------
Northeast

DoThat
--------------------
Northeast

Change it once more to Southeast, only this time, also set the readonly parameter to true. This prevents the value from being changed again during this session:

EXEC sp_set_session_context @key = N'UsRegion', @value = N'Southeast', @read_only = 1

And the change is reflected when we run the procedures again:

EXEC DoThis
EXEC DoThat

DoThis
--------------------
Southeast

DoThat
--------------------
Southeast

Finally, try to change the region to Northwest:

EXEC sp_set_session_context @key = N'UsRegion', @value = N'Northwest'

You see that we can’t. because it’s locked in session context:

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

There’s absolutely no way the value can be changed unless you kill the connection like we saw earlier, in which case of course you lose all the values in session context.

Just DIE Please! Introducing Drop If Exists (DIE) in SQL Server 2016

In SQL Server 2016, Drop If Exists (DIE) is a handy new T-SQL language enhancement that eliminates the need to test before you drop.

So, if you need to delete a table, or a stored procedure, but you don’t know if it exists or not, then you’re used to writing code that says, “If the table exists, then drop it,” or “if the stored procedure exists, then drop it.” We’ve all been writing code like this for years, but that doesn’t mean it’s been fun:

• IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL
   DROP TABLE dbo.Product

• IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trProductInsert')
   DROP TRIGGER trProductInsert

So now, thankfully, we can leave the testing to SQL Server:

• DROP TABLE IF EXISTS dbo.Product

• DROP TRIGGER IF EXISTS trProductInsert

It doesn’t get much simpler than this. It’s really just an ordinary DROP statement; you just inject the new syntax IF EXISTS in the middle, and you’re done.

This new feature is available for just about anything you need to drop, so not just tables and triggers, but all of the following:

  • AGGREGATE
  • ASSEMBLY
  • DATABASE
  • DEFAULT
  • INDEX
  • PROCEDURE
  • ROLE
  • RULE
  • SCHEMA
  • SECURITY POLICY
  • SEQUENCE
  • SYNONYM
  • TABLE
  • TRIGGER
  • TYPE
  • VIEW

So no, DIE isn’t really any major new big feature, but if you like neat code – as I do – then it’s welcome just the same.