Implementing Global Variables in SQL Server: Methods and Best Practices

Understanding Variable Scope in SQL Server

In Transact-SQL, variable scope is strictly bound by batch boundaries. When code is separated by GO statements, each batch executes within its own context. Variables declared in one batch become inaccessible in subsequent batches. While this design ensures code isolation and security, it presents challenges for scenarios requiring data sharing across multiple batches.

SQLCMD Mode Solution

For scripts requiring data persistence across batches, SQLCMD tool and its integrated mode in SQL Server Management Studio (SSMS) offer an effective solution. SQLCMD variables are parsed by the client tool before script execution, making them immune to T-SQL batch boundaries.

After enabling SQLCMD mode, variables can be defined and used as follows:

:setvar ConfigValue 42
SELECT * FROM Production.Data WHERE Threshold > $(ConfigValue)

This approach maintains variable values throughout script execution and supports cross-database usage. However, it requires SQLCMD-compatible environments and predetermined variable values that cannot be modified at runtime.

Global Temporary Tables Alternative

For more flexible runtime data sharing, global temporary tables (prefixed with ##) provide a viable alternative. These tables are visible to all connections until the creating connection closes and all referencing connections release them.

CREATE TABLE ##ApplicationSettings (
    SettingKey NVARCHAR(100) PRIMARY KEY,
    SettingValue NVARCHAR(MAX),
    LastUpdated DATETIME DEFAULT GETDATE()
)

INSERT INTO ##ApplicationSettings (SettingKey, SettingValue) 
VALUES ('TimeoutSeconds', '30'), ('LogLevel', 'DEBUG')

-- Access in different batch or connection
SELECT * FROM Audit.Trail 
WHERE Severity = (SELECT SettingValue FROM ##ApplicationSettings WHERE SettingKey = 'LogLevel')

This method allows dynamic value modification at runtime but requires consideration of locking contention during concurrent access and is subject to connection-based lifecycle management.

Traditional CONTEXT_INFO Approach

SQL Server provides the CONTEXT_INFO system function for storing up to 128 bytes of binary data within a session. While limited in capacity and type flexibility, it remains useful for simple configuration storage.

DECLARE @SessionConfig VARBINARY(128)
SET @SessionConfig = CONVERT(VARBINARY(128), 'Theme:Dark;Format:JSON')
SET CONTEXT_INFO @SessionConfig

-- Retrieve in other parts of session
SELECT CONVERT(NVARCHAR(128), CONTEXT_INFO()) AS CurrentConfig

Modern SESSION_CONTEXT Implementation

Starting with SQL Server 2016, enhanced session context management was introduced through the sp_set_session_context stored procedure and SESSION_CONTEXT function. This allows key-value pair storage with data type support and read-only protection.

-- Set session context values
EXEC sp_set_session_context @key = N'UserPreferences', @value = N'{"theme":"dark","notifications":true}';
EXEC sp_set_session_context @key = N'ConnectionLimit', @value = 50, @read_only = 1;

-- Retrieve session context
SELECT SESSION_CONTEXT(N'UserPreferences') AS Preferences,
       SESSION_CONTEXT(N'ConnectionLimit') AS MaxConnections;

This approach addresses many limitations of CONTEXT_INFO, offering improved type safety and access control. The read-only flag is particularly valuable for configuration parameters that should remain constant during a session.

Practical Application Scenarios

When selecting an appropriate "global variable" implementation, consider these factors:

1. Data Persistence Requirements: Whether data needs to survive across connections
2. Concurrent Access Patterns: Multiple connections accessing the same data simultaneously
3. Data Type Complexity: Required data structures and types
4. Security Considerations: Need for access control protection
5. Environmental Constraints: Target environment capabilities and limitations

For simple script-level variable sharing, SQLCMD mode provides the most straightforward solution. For runtime data that must persist across batches within the same session, SESSION_CONTEXT offers the best balance. When data sharing across different sessions is required, global temporary tables or persistent configuration tables may be more appropriate.

Performance Considerations and Best Practices

Regardless of the chosen approach, performance implications should be considered:

• Avoid frequent access to global temporary tables in high-frequency queries
• Utilize SESSION_CONTEXT read-only flags appropriately to reduce locking contention
• Implement proper indexing for global temporary tables
• Regularly clean up unnecessary global temporary objects

By understanding the principles and appropriate use cases for each approach, developers can select optimal "global variable" implementations that maintain code clarity while meeting business requirements effectively.