New SESSION_CONTEXT() Support in SQL Server 2016

SQLServerF1

There are many application which have requirement to store and retrieve session context information from SQL Server. Session context information is very useful in cases which can be used to store information specific to each user or their current state of the application. This is required in some applications to control the logic in Transact-SQL statements. Prior to SQL Server 2016, application developers used to rely mostly on CONTEXT_INFO function or sometimes on TSQL session variables, but both these have their own disadvantages like, Transact-SQL variables, scope is limited to that current Transact-SQL batch, stored procedure, trigger, or user-defined function. SQL/Application developers were using the CONTEXT_INFO function for retrieving the session context for the current session. Also, CONTEXT_INFO allows to retrieve session context values for all current sessions and batches from the context_info columns in the sys.dm_exec_requests or sys.dm_exec_sessions dynamic management views. Inorder to run queries against these views, required SELECT and VIEW SERVER STATEE permissions on the SQL Server instance, but these permissions were not required anymore when we use the CONTEXT_INFO function.

But this CONTEXT_INFO function has its own limitations too like, It is a single and a binary value, therefore is very difficult to work with because we will need to convert the data from binary to human readable format and also the involves the complexity of storing multiple values in a single binary representation, which will be difficult to use, another limitation include the size supported was only 128 bytes for the connection which was not liked my many developers, this CONTEXT_INFO also has another security concern too as it allows the users to overwrite the data at any time, and also this does not work well with SQL Azure databases. So, upon the feedback from the developer community, Microsoft has introduced new built-in function called SESSION_CONTEXT() starting with Microsoft SQL Server 2016. This is not a new innovation by Microsoft, but there are equivalents in other RDBMS like Oracle.

The way SESSION_CONTEXT function is different from CONTEXT_INFO is that the SESSION_CONTEXT uses key-value pairs to store the session data and the keys are of SYSNAMEE type where as the values are SQL_VARIANT type. Now, the size of the data that can be store has been increased/set to 256 KB, which is significantly greater than the size of data allowed with CONTEXT_INFO. Also, the security concerns has been addressed, wherein we can set the key as read only, so the value will not be able to be changed once it has been established. Also, as this has been introduced after SQL Azure was launched, Microsoft has kept the SQL Azure databases in mind and made this work similarly on both on-premise and on SQL Azure databases. We need to set the key-value pairs using the stored procedure called as sys.sp_set_session_context.
@key – is the key which is being set, and is of type sysnamee. The maximum key size allowed 128 bytes.
@value – is the value for the specified key, and it is of type sql_variantt. Setting this value of NULL will free the memory. The maximum size is 8,000 bytes.
@read_only – This allows zero or one. This is a flag of type bit. If 1 is set, then the value for the specified key cannot be changed again on this logical connection. If 0 (default), then the value can be changed.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

JSON (JavaScript Object Notation) Support with SQL Server 2016

SQLServerF1

There have been many new features introduced with SQL Server 2016 and many enhancements and additional support to new features, etc. One such interesting change with Microsoft SQL Server 2016 is native support for JSON. JSON stands for JavaScript Object Notation. If you are wondering what is this JSON (JavaScript Object Notation), is is an open and text-based data exchange format, that provides a standardized data exchange format better suited for Ajax-style web applications. In other words, JSON is a data interchange format that has become popular for moving data between systems. There was no support for JSON feature prior to SQL Server 2016, so the application developers had to fall on to use third party tools or workarounds to achieve the output in this format.

One of the popular language which many of us are aware for exchanging data between different heterogeneous systems is XML. Since many versions of SQL Server has been supporting XML by allowing queries which deal with XML data to store, retrieve or manipulate the XML data using SQL Server TSQL queries, specifically designed to handle XML data. JSON is another such data format like XML, which is also used to exchange data between different heterogeneous systems. Initially JSON was developed specifically for javascript, but later it has become as an independent language. As Microsoft wants to spread the usage of SQL Server across different platforms, there has been many changes like support to running SQL Server on linux and support for languages like JSON. Although, SQL Server 2016 supports JSON language, but that does not mean, XML is no more supported. Microsoft SQL Server newer versions will continue to support both XML and JSON languages and in future, we may see even more languages added to this list, this will help SQL Server scale new heights and can be used with any applications.

Format of JSON looks like below

JSON
[ 
   { "name": "Test", "skills":["SQL","C#","Azure"] },
   { "name": "Mike", "surname": "Taite" }
]

SQL Server offers many built-in functions and operators that lets us do the things like,
– Parse JSON text and read or modify the values.
– Convert arrays of JSON objects into table format.
– Use any TSQL queries on the converted JSON objects.
– Format the results of TSQL queries in JSON format.

Some of the key capabilities that SQL Server provides related JSON include,
– Use the JSON_VALUE function to extract a scalar value from a JSON string
– Use JSON_QUERY to extract an object or an array
– Use the ISJSON function to test whether a string contains valid JSON.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings