SQL Session Context

Question

SQL Session Context

Avi1

SSCrazy

Points: 2323
More actions
November 14, 2017 at 11:49 pm

#341377

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

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 1

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: 18729 More actions · Answer 2

HappyGeek

SSCoach

Points: 18729

November 14, 2017 at 11:57 pm

#1968115

Good question many thanks!

...

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 3

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

Darko Martinović Hall of Fame Points: 3545 More actions · Answer 4

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: 5161 More actions · Answer 5

SQL Surfer '66

SSCertifiable

Points: 5161

November 15, 2017 at 4:19 am

#1968153

Excellent. Thank you very much.

paul s-306273 SSChampion Points: 10730 More actions · Answer 6

paul s-306273

SSChampion

Points: 10730

November 15, 2017 at 5:08 am

#1968158

Nice QOTD.

Budd Hall of Fame Points: 3781 More actions · Answer 7

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 More actions · Answer 8

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: 23660 More actions · Answer 9

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 More actions · Answer 10

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: 286590 More actions · Answer 11

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: 23660 More actions · Answer 12

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: 3897 More actions · Answer 13

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: 11368 More actions · Answer 14

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!