SQL Session Context

  • Avi1

    SSCrazy

    Points: 2323

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71852

    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”

  • HappyGeek

    SSCoach

    Points: 18684

    Good question many thanks!

    ...

  • allinadazework

    SSCarpal Tunnel

    Points: 4365

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

  • Darko Martinović

    Hall of Fame

    Points: 3545

    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.

  • SQL Surfer '66

    SSCertifiable

    Points: 5159

    Excellent. Thank you very much.

  • paul s-306273

    SSChampion

    Points: 10615

    Nice QOTD.

  • Budd

    Hall of Fame

    Points: 3769

    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';
    SELECT SESSION_CONTEXT(N'language');
    make this work ?
    DECLARE @i INT = 100;
    SELECT @i;
    GO
    SELECT @i;

  • Darko Martinović

    Hall of Fame

    Points: 3545

    Hi Budd,

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

    GO
    SELECT
        CAST(SESSION_CONTEXT(N'i') AS INT)
    GO
  • x

    SSC-Insane

    Points: 23578

    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.

  • Darko Martinović

    Hall of Fame

    Points: 3545

    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
    GO
    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.

  • Sean Lange

    SSC Guru

    Points: 286536

    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/

  • x

    SSC-Insane

    Points: 23578

    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!!!"

  • Thomas Franz

    Hall of Fame

    Points: 3649

    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.

  • Toreador

    SSChampion

    Points: 11257

    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 17 total)

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