SQL Session Context

  • Comments posted to this topic are about the item SQL Session Context

  • Nice one, Avinash - never thought about using this , so learned something.
    even though, doubt persists about a feasible reason for doing something in this way.

    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Good question many thanks!


  • Good question. Very useful to learn a bit more about this functionality.

  • Good question and good feature.
    Unfortunately it is not backward compatible. It works for SQL Server 2016+ versions.
    For that reason this feature is very limited.
    Further on as Microsoft says : 'The total size of the session context is limited to 256 kb. If set a value that causes this limit to be exceeded, the statement fails. You can monitor overall memory usage in sys.dm_os_memory_objects (Transact-SQL).
    You can monitor overall memory usage by querying sys.dm_os_memory_cache_counters (Transact-SQL) as follows: SELECT * FROM sys.dm_os_memory_cache_counters WHERE type = 'CACHESTORE_SESSION_CONTEXT'; '

    This is another limitation!

    In my opinion most users will continue to use temporary tables.

  • Excellent. Thank you very much.

  • Nice QOTD.

  • Very interesting Question. and of course now I have to play with that and figure out how it works, and I'm unable to do that in the amount of time I allotted myself.
    So how does this 
    EXEC sp_set_session_context 'language', 'English';
    make this work ?
    DECLARE @i INT = 100;
    SELECT @i;
    SELECT @i;

  • Hi Budd,

    DECLARE @i AS INT = 100
    EXEC sp_set_session_context @key = N'i'
           ,@value = 100

  • one of the things I was interested in are the wrong answers, it would be cool to be able to see explanations on those. I accidently got this question right because I had no clue and made a guess.

  • Very good idea Patric,
    QotD will become more popular in that case. 
    In short sys.server_event_session lists all the event session definitions that exists in SQL Server.
    SESSIONPROPERTY returns SET options settings of a session. 
    And finally there is no way to define global variables in T-SQL. Besides temporary tables, I mention above, there is possibility to use SQLCMD mode in SSMS. 
    Or SQLCMD tool. 
    In that case the following snippet will produce the correct value. 

    :setvar I 10
    DECLARE @myTest AS INT = $(I)
    SELECT @myTest
    There is a possibility to use CONTEXT_INFO as well. This persist only a binary value limited to 128 bytes.

  • I am shocked that 39% of respondents thought you can have a global variable by defining it with @@.

    This is a great QOTD. Well done.


    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange - Wednesday, November 15, 2017 1:19 PM

    I am shocked that 39% of respondents thought you can have a global variable by defining it with @@.

    This is a great QOTD. Well done.

    LOL I know I was tempted, like "man, global variables??? sign me up!!!"

  • Our database uses triggers on many databases. Some time ago, when the developers had to run a special INSERT/UPDATE/DELETE, which should not trigger the trigger :-), the always disabled the trigger(s). This could be dangerous, even when used in a maintenance window, since there was a chance, that they forgot to enable it again (and disabling a trigger, while users / apps are working on the database is VERY bad).

    Today every trigger starts with IF CONTEXT_INFO() = 0x1000 RETURN
    When the developers wants to run a triggerless statement, they only need to
    SET CONTEXT_INFO 0x1000;
    and the trigger is 'disabled' for this particular session only, while all other sessions / users are unaffected (the 0x1000 is a 'random' value - we could have used any other too).

    Caution: INSTEAD-OF-triggers must not RETURN when CONTEXT_INFO() = 0x1000, but have to run the originial, "unchanged" statement, so that means more coding. On the other hand are instead-of-triggers even worser than AFTER triggers.

    God is real, unless declared integer.

  • Sean Lange - Wednesday, November 15, 2017 1:19 PM

    I am shocked that 39% of respondents thought you can have a global variable by defining it with @@. .

    I didn't have the faintest idea of the answer so went for that one on the basis of wishful thinking, in the vain hope that it was analogous to global temp tables!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply