SQL Server 2016: New T-SQL Features

In this article, I would like to describe several new T-SQL features available in SQL Server 2016: DROP IF EXISTS, SESSION_CONTEXT, MAXDOP, FORMATMESSAGE, COMPRESS and DECOMPARESS, DATEDIFF_BIG, AT TIME ZONE, FOR JSON and other.

1. Objects can DIE: the new DROP IF EXISTS feature

To drop the table, we need to execute the following code:
Starting from SQL Server 2016 CTP3 you can use new DROP IF EXISTS statement instead of IF wrappers. In case an object does not exist, DROP IF EXISTS will not fail and execution will continue.
Also, there is an ability to delete child elements:
DROP IF EXISTS is available for the following objects:

AGGREGATE PROCEDURE TABLE
ASSEMBLY ROLE TRIGGER
VIEW RULE TYPE
DATABASE SCHEMA USER
DEFAULT SECURITY POLICY VIEW
FUNCTION SEQUENCE
INDEX SYNONYM

2. SESSION_CONTEXT

ASP.NET provides the Session object. You can use this object to store and retrieve values for a user as they browse ASP.NET pages in a web application within a particular session.

There are a number of reasons why you need to use session variables within SQL Server. For example, you may need to identify users for auditing purposes, or you need to preserve some information that can be accessible within a module, such as a trigger or view, which does not accept parameters. Or you simply want to maintain session-scoped data that is costly to look up.

SQL Server 2016 allows you to store multiple key and value pairs which are accessible within a particular session. These pairs can be set with help of the sp_set_session_context system stored procedure and retrieved (one at a time) with help of the SESSION_CONTEXT built in function.

Before SQL Server 2016, you only could use CONTEXT_INFO(). Howevet, it has a number of restirctions:

  • It is complicated to store multiple values in a single binary representation.
  • It is limited to 128 bytes per connection.
  • Data can be overwritten by a user at any time. This is kind of a security problem.
  • Azure SQL Database does not support the same behaviour. In case no value has been set, it returns a random GUID.

Setting a session variable

This is a demo:

The result is 128.

Trying to update a read-only value

We can use the @read_only argument to set a read only key:

Trying to update:

We get the following error:
Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 10 Cannot set key ‘user_ID’ in the session context. The key has been set as read_only for this session.

3. The new MAXDOP option in DBCC CHECKDB

By default, the number of threads checking the logical and physical integrity of all the objects in a specified database is equal to the number of logical CPU cores. The new MAXDOP feature allows you to manage the number of threads, to maintain overall server performance at the appropriate level.

The same functionality is available for DBCC CHECKTABLE and DBCC CHECKFILEGROUP

MAXDOP overrides the maximum degree of parallelism configuration option of sp_configure for the statement. The MAXDOP can exceed the value configured with sp_configure. If MAXDOP exceeds the value configured with Resource Governor, the engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP. All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint.

4. FORMATMESSAGE

In previous versions, FORMATMESSAGE constructed a message from strings located insys.messages. Now you can also supply your own string:

SQL_Server_2016_FORMATMESSAGE

 

5. COMPRESS and DECOMPRESS

Another interesting feature is the ability to compress and decompress fields during DML (either select, insert, or update) using the COMPRESS and DECOMPRESSfunctions. Compressed fields cannot be indexed. It is a good option if you are not going to perform search across the compressed fields.
Here is an example of compression:

We have 1012 Text_Mix_Page, besides the other types of pages. Let’s compress fields:

The DECOMPRESS function result type is VARBINARY. You need to case the result to see the original data.

SQL_Server2016_decompress

The compression utilizes the GZIP algorithm. You can decompress the data in the client application, rather than in the query.

6. DATEDIFF_BIG

Erland Sommarskog requested this feature back in 2008 at the Microsoft connect website. DATEDIFF_BIG returns the count (signed big integer) of the specified datepart boundaries crossed between the specified startdate and enddate. Previous versions of SQL Server show the following error (When the date range is set too high):
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Now you can use:

7. AT TIME ZONE

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone. If inputdate is provided as a datetimeoffset value, than AT TIME ZONE clause converts it into the target time zone using time zone conversion rules. AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones.
With this function you can display the time in the specified time zone:

Result:
———————————-
2016-02-07 17:54:02.1354687 -03:00

8. JSON

I have already wrote about JSON Support in SQL Server 2016. SQL Server 2016 along with XML, now supports the JSON format. You can convert tabular data to JSON using the FOR JSON clause.

SQLServer2016_FORJSON

9. ALTER COLUMN ONLINE

ALTER TABLE can now alter many columns while the table remains online, using WITH (ONLINE = ON | OFF).

10. TRUNCATE (partitioned) TABLE

Now it removes all rows from a table or specified partitions of a table, without logging the individual row deletions. The following code truncates specified partitions of a partitioned table. The WITH (PARTITIONS (2, 4, 6 TO 8)) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.

11. CURRENT_TRANSACTION_ID

The function returns the transaction ID of the current transaction in the current session. The following code returns the transaction ID of the current session:

Summary

There is no doubt it is going to be the very interesting release. We are anticipating it!

Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.
Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.