How can I set a session variable that is available in multiple batches?

Question

I have a large database script migrating multiple databases of the same structure to one destination database. This destination database is more generic so it is able to store the data from the different source databases. I use a variable to keep track of the current Entity being migrated so I know what ID to insert in the destination table.

At this moment the migration performance is really bad. To be able to profile the script better I'd like to split up the script by placing 'go' after each table migration but this destroys the variable. Is there a way to declare a variable that is accessible for the whole connection/session? Just like a temp #table is?

Solution 1

Query:

DECLARE
      @UserID TINYINT = 1
    , @LocaleID INT = 123456789
    , @ApplicationID BIGINT = 123456789123456789
    , @UserName VARCHAR(10) = 'User1'
    , @context VARBINARY(128)

SELECT @context = 
    CAST(@UserID AS BINARY(1)) + 
    CAST(@LocaleID AS BINARY(4)) + 
    CAST(@ApplicationID AS BINARY(8)) +
    CAST(@UserName AS BINARY(10))

SET CONTEXT_INFO @context

GO

SELECT 
      UserID = CAST(SUBSTRING(ci, 1, 1) AS TINYINT)
    , LocaleID = CAST(SUBSTRING(ci, 2, 4) AS INT)
    , ApplicationID = CAST(SUBSTRING(ci, 6, 8) AS BIGINT)
    , UserName = CAST(SUBSTRING(ci, 14, 10) AS VARCHAR)
FROM (SELECT ci = CONTEXT_INFO()) t

Result:

UserID      LocaleID    ApplicationID      UserName
----------- ----------- ------------------ ------------------------------
1           123456789   123456789123456789 User1     

Additional info:

MSDN - CONTEXT_INFO

Solution 2

With SQL Server 2016 you can use sp_set_session_context:

EXEC [sys].[sp_set_session_context] @key = 'SecurityObjectUserID'
                                   ,@value = @SecurityObjectUserID
                                   ,@read_only = 1;  

to set a variable, and the following to read it:

SELECT @SecurityObjectUserID = CONVERT(BIGINT,SESSION_CONTEXT(N'SecurityObjectUserID'));

Note, we can mark a variable to be read_only. In this way, other routines are not able to change it.

Solution 3

SET CONTEXT_INFO 0x01010101
GO
SELECT context_info 
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO

this way you can fake global/session variables For more info: Context_info

Comments

[Damien_The_Unbeliever]

Seems a tad unsafe to assume that ints can be packed into binary(2)s.

[Excelosaurus]

Fun fact (tested on SQL Server 2016 only): SESSION_CONTEXT() is not case-sensitive except for the key's last character. For example, if you have a value keyed by "toto", you can retrieve it by all variations of this key but the ones ending with capital 'O'.