Notes on SQL

Background
For a long time, web developers have been able to store session information. SQL Server has CONTEXT_INFO, which is a very poor implementation of a session variable. However, with SQL Server 2016 there is now a far more flexible SESSION_CONTEXT feature available.

The procedures associated with SESSION_CONTEXT enable values to be passed within batches that are within the same connection. These values can be edited or ‘locked’, depending upon supplied parameters.

Test Environment

For this demonstration a View will be used, to show that is effectively possible to pass parameters. This method will also work for triggers but it is just easier to implement a View for a simple test.

Listing 1: Test Data

CREATE DATABASE Session_Test;
GO

CREATE TABLE dbo.TestData
	(
		ID INT IDENTITY(1, 1) NOT NULL ,
		PartNo NVARCHAR(10) NOT NULL ,
		PartName NVARCHAR(50) NOT NULL
	);
GO

INSERT INTO dbo.TestData (PartNo ,
			PartName
				)
VALUES ( N'00-123456', N'Test Product One' ) ,
	( N'01-98765', N'Test Product Two' );
GO

CREATE VIEW dbo.ShowTestData
AS
	SELECT ID ,
		   PartNo ,
		   PartName ,
		   SESSION_CONTEXT(N'test') AS [Test Context] ,
		   SESSION_CONTEXT(N'test2') AS [Test2 Context] ,
		   SESSION_CONTEXT(N'test3') AS [Test3 Context]
	FROM   dbo.TestData;
GO

Now to set the SESSION_CONTEXT values:

Listing 2: Setting SESSION_CONTEXT

EXEC sys.sp_set_session_context @key = N'test2' ,
				@value = N'Test Value2' ,
				@read_only = 0;

EXEC sys.sp_set_session_context @key = N'test3' ,
				@value = N'Test Value3' ,
				@read_only = 1;

Listing 2 shows how to set a SESSION_CONTEXT value with sp_set_session_context.
The ‘@key’ parameter is a SYSNAME value, which cannot exceed 128 bytes. Oddly enough it does not have to be specified as NVARCHAR but if you don’t specify NVARCHAR when retrieving it (with SESSION_CONTEXT(N’name‘) then it will error with Argument data type varchar is invalid for argument 1 of session_context function.
The ‘@value’ parameter cannot exceed 8000 bytes – a considerable improvement over CONTEXT_INFO.
The ‘@Read_Only’ parameter, when set to 1 will prevent that session value from being changed.

Although the View refers to three different session context values, only two have been created. This is to demonstrate that selecting a value that does not exist will return NULL, instead of an error.

Code Execution

Listing 3: Select from the View

SELECT ID ,
        PartNo ,
        PartName ,
        [Test Context] ,
        [Test2 Context] ,
        [Test3 Context] FROM dbo.ShowTestData;
GO

Picture 1: Results from the View

The values associated with ‘test2’ and ‘test3’ are selected within the view, with NULL where it attempted to select a value for ‘test’.

Changing a SESSION_CONTEXT Value

The value associated with ‘test2’ can be changed by using sp_set_session_context and then selecting from the view again:

Listing 4: Update Session Value

EXEC sys.sp_set_session_context @key = N'test2' ,
				@value = N'Test value updated' ,
				@read_only = 0;
GO

SELECT ID ,
	   PartNo ,
	   PartName ,
	   [Test Context] ,
	   [Test2 Context] ,
	   [Test3 Context] FROM dbo.ShowTestData;
GO

Picture 2: Results from View with Updated Session Value

Updating a Read_Only SESSION_CONTEXT

When a SESSION_CONTEXT has been created with ‘@read_only’ set to 1, an error will be raised if an attempt is made to change the value:

Listing 5: Attempting to Change a Read Only Value

EXEC sys.sp_set_session_context @key = N'test3' ,
				@value = N'Test Value3 Updated' ,
				@read_only = 1;
GO

Executing the code in Listing 5 will result in the error message Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 34]
Cannot set key ‘test3’ in the session context. The key has been set as read_only for this session. This is because when this session context was set the ‘@read_only’ parameter was set to 1 and therefore prevents alteration. Try executing the code in Listing 5 with @read_only as zero and you will get the same error message.

Row-Level Security
A slightly more realistic example for using SESSION_CONTEXT is with Row-Level Security – introduce in SQL Server 2016. A basic example of this is in this Microsoft article on Row-Level Security.

References
SESSION_CONTEXT
sp_set_session_context
CONTEXT_INFO
Row-Level Security