Understanding SESSION_CONTEXT in SQL Server

SESSION_CONTEXT is a feature introduced in SQL Server 2016 that allows developers to store key-value pairs that are accessible throughout the duration of a database session.

Key Characteristics of SESSION_CONTEXT

Session-scoped: The data persists for the duration of the session.
Key-value based: You can store a limited number of key-value pairs.
Secure: Cannot be changed by another session.
Fast: Works in memory and is lightweight.

Syntax

EXEC sys.sp_set_session_context @key = 'YourKey', @value = 'YourValue';

SELECT SESSION_CONTEXT(N'YourKey');

Example

-- Set a value
EXEC sys.sp_set_session_context @key = 'UserId', @value = '12345';

-- Retrieve the value
SELECT SESSION_CONTEXT(N'UserId') AS UserId;

Use Cases of SESSION_CONTEXT

Pass context from application to SQL Server (e.g., User ID, Tenant ID).
Implement row-level security or audit logging.
Maintain state between stored procedures or triggers.

SESSION_CONTEXT vs CONTEXT_INFO

Feature	SESSION_CONTEXT	CONTEXT_INFO
Introduced in	SQL Server 2016	Earlier versions
Data type	Key-value pairs	Binary (up to 128 bytes)
Readability	Human-readable (via SELECT)	Binary decode needed
Security	Session-scoped, isolated	Can be overwritten

Limitations of SESSION_CONTEXT

Maximum of 256 key-value pairs per session.
Key names are case-insensitive.
Value can be up to 8,000 bytes in size.

SESSION_CONTEXT in Triggers

You can use SESSION_CONTEXT inside triggers to check if a certain flag is set by the session and control the trigger behavior.

-- Inside a trigger
IF SESSION_CONTEXT(N'SkipTrigger') = 1
    RETURN; -- Skip trigger logic

Best Practices

Always use consistent key names.
Set values at the beginning of a session or request.
Use in combination with application logic to improve traceability.
Avoid overusing it for large data or unnecessary keys.

SESSION_CONTEXT Lifespan

It lasts for the duration of a session or until the connection is closed. For connection pooling scenarios, ensure values are reset at the start of every request.

Multi-user Consideration

Each session has its own isolated SESSION_CONTEXT. Values set by one session are not visible or accessible to other sessions.

Conclusion

SESSION_CONTEXT is a powerful feature for managing session-level metadata in SQL Server. It is especially useful for modern multi-tenant applications, logging, and security implementations. By integrating with your application logic, it enhances flexibility and control within SQL stored procedures and triggers.