NEW T-SQL FEATURES IN SQL SERVER 2016- IX [ SESSION_CONTEXT() ) ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

This function will returns the value of the specified key in the current session context. The value is set by using the sp_set_session_context (Transact-SQL) procedure.

Notes:-

1. If the key is not incorrect then the return value will be NULL.
2. This function will return the sql_variant data type.
3. This function provides the ability to maintain session variables within SQL Server just like we have in .NET.
4. The maximum size of the session context is limited to 256 kb.
5. If the sp_set_session_context is read_only only then it cannot be updated later.
EXEC sp_set_session_context @key = ‘pawan’, @value=’10’, @read_only = 1
6. In the syntax of SESSION_CONTEXT the N is compulsory otherwise you will receieve an error.

Example of Setting and Accessing SESSION_CONTEXT

--

EXEC sp_set_session_context 'pawan', 10;  
GO
--Commands completed successfully.


SELECT SESSION_CONTEXT(N'pawan');  
GO


----------------------------
10


--

Example of Accessing non existing SESSION_CONTEXT

--

EXEC sp_set_session_context 'pawan', 10;  
GO
--Commands completed successfully.


SELECT SESSION_CONTEXT(N'pawan1');  
GO


--------------------
NULL

(1 row affected)


--

Example of Updating read_only SESSION_CONTEXT

--

EXEC sp_set_session_context 'pawan', 10 , @read_only = 1  
GO

SELECT SESSION_CONTEXT(N'pawan')


-----------------------------
10

(1 row affected)


EXEC sp_set_session_context 'pawan', 11
GO

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 12]
Cannot set key 'pawan' in the session context. The key has been set as read_only for this session. 

--

Example of Compulsory N(NVARCHAR)

--

EXEC sp_set_session_context 'pawan', 10 , @read_only = 1  
GO

SELECT SESSION_CONTEXT('pawan')

Msg 8116, Level 16, State 1, Line 4
Argument data type varchar is invalid for argument 1 of session_context function.


SELECT SESSION_CONTEXT(N'pawan')



----------------------------------
10

(1 row affected)


--

Refer Microsoft LINK below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com