SQL Server  

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.

Membership not found