散亂格部落

散亂格

2019-03-11

CONTEXT_INFO 和 SESSION_CONTEXT整理筆記

CONTEXT_INFO 和 SESSION_CONTEXT整理筆記

  CONTEXT_INFO SESSION_CONTEXT
Applies To -SQL Server (Starting with 2008)
-Azure SQL Database		 -SQL Server (Starting with 2016)
-Azure SQL Database
Return Value Null (if not set) Null (if no value set for that key)
Required Premission

SELECT and VIEW SERVER STATE permissions:
-sys.dm_exec_requests
-sys.dm_exec_sessions
-sys.sysprocesses
The context_info value also stored in the system views above

 User only able to read session context for their session
Multiple Active Result Sets (MARS) Handle 仍需了解
-Only return the new value when the batch completed
-Return the new context value that runs in the same batch		 仍需了解
-Only return the new value when the batch completed
Accept Type 128 bytes of binary information (varbinary or binary)
Cannot assign Null since the views value is not nullable		 Key size: 128 bytes
Value: 8,000 bytes (sql_variant) / null (frees the memory)
Read_only: 0 | 1 (bit) (Default as 0)
Set 1 that the value cannot be changed again in the same connection
Get Method SELECT CONTEXT_INFO(); SELECT SESSION_CONTEXT(N'key');
Set Method / Syntax

SET CONTEXT_INFO { binary_str | @binary_var }

Examples:
SET CONTEXT_INFO 0x01010101;

DECLARE @BinVar varbinary(128);
SET @BinVar = CAST(REPLICATE( 0x20, 128 ) AS varbinary(128) );
SET CONTEXT_INFO @BinVar;

sp_set_session_context [ @key= ] N'key', [ @value= ] 'value' [ , [ @read_only = ] { 0 | 1 } ] [ ; ]

Examples:
EXEC sys.sp_set_session_context @key = N'language', @value = 'English';

EXEC sys.sp_set_session_context @key = N'user_id', @value = 4, @read_only = 1;
 
Remarks When you issue SET CONTEXT_INFO in a stored procedure or trigger, unlike in other SET statements, the new value set for the context information persists after the stored procedure or trigger is completed. -Total size of session context is 1 MB
-Cannot set read-only when multiple MARS batch are active on same connection

Remarks:
Multiple Active Result Sets (MARS): Allow multi-batch / excutsion in one connection

參考:
[SQL SERVER]SQL2016-管理session context
https://dotblogs.com.tw/ricochen/2016/11/30/211605
CONTEXT_INFO (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/context-info-transact-sql?view=sql-server-2017
SESSION_CONTEXT (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/session-context-transact-sql?view=sql-server-2017

系列文章

標籤雲