SQL Server 2016 CTP3.1 – What’s New for Developer?

Not that long ago, I remember Microsoft’s CEO Satya Nadella has been announcing a new version of SQL Server 2016. And all of a sudden fresh Community Technology Preview versions started appearing one by one (currently the latest version is CTP3.1). Having tested the new version, I would like to share my impressions.

I will now review the new T-SQL syntax features of SQL Server 2016: JSON, GZIP, DROP IF EXISTS, TRUNCATE TABLE by partitions…

# 1 – DROP IF EXISTS

Earlier, I had to check before deleting an object:

But now, there is a shorter syntax:

In the easy way, you can delete child objects:

DROP statements can be combined for one parent object:

DROP IF EXISTS is supported for almost all user objects (the complete list can be found here):

# 2 – SESSION_CONTEXT

In my practice, there were tasks on sharing parameters within a user session. Previously, I had to use CONTEXT_INFO, which was limited in size to 128 bytes:

Now, everything has become a bit more comfortable with the new SESSION_CONTEXT function, which allows to store 256KB per session:

# 3 – CHECKDB + MAXDOP

By default, upon execution of DBCC CHECKDB command, the number of threads equal to the number of logical cores was used. Now, you can limit the number of threads so as not to reduce server performance on the whole:

Similar functionality is added to DBCC CHECKTABLE and DBCC CHECKFILEGROUP:

DBCC CHECKDB was slightly optimized in terms of checking filtered indexes and the PERSISTED COMPUTED columns. Additionally, the time for checking tables containing a large number of partitions was reduced.

# 4 – FORMATMESSAGE

In the previous versions, the FORMATMESSAGE function could use only previously added custom messages:

It is now possible to specify a random mask:

Many routine operations (like quoting or string concatenation) can be done more nicely:

# 5 – COMPRESS & DECOMPRESS

The integrated support for GZIP: COMPRESS and DECOMPRESS appeared in the new edition. In decoding, it is important to check the correct datatype to which the output is converted:

Here, I encode the ANSI string and try to decode the received value:

# 6 – DATEDIFF_BIG

In SQL Server 2008, new parameters, MICROSECOND and NANOSECOND, were added to the DATEDIFF function, but when an excessively large date range was specified:

this could lead to an error:

The new DATEDIFF_BIG feature was added for such situations:

# 7 – AT TIME ZONE

In CTP3.0, a new system presentation appeared:

where you can get a list of time zones:

Using AT TIME ZONE, you can display the time in a given time zone:

which can be parameterized:

What are the benefits of this feature? For example, you can display the time in other time zones based on the local time zone:

# 8 – JSON

Support for JSON is one of the major features of SQL Server 2016. Starting from CTP2.0, you can generate JSON, by analogy with XML. The two constructions FOR JSON AUTO and FOR JSON PATH are supported:

For the NULL values to be included in JSON upon generation, use INCLUDE_NULL_VALUES option:

NVARCHAR should be used for storing JSON, since no particular data type is provided:

You can use OPENJSON to make a selection from JSON. If there is only one record, then the result will be a «key-value»:

This behavior can be used as “one more variant” of the string split:

In case of several records:

the result is as follows:

Parse data from JSON is quite simple:

Use JSON_VALUE if you want to get a scalar expression:

You can use ISJSON, if you want to make sure that the text is JSON:

For extracts an object or an array from a JSON string use JSON_QUERY:

Also need keep in mind, that no specific type of indexes are provided for JSON, but there is a possibility to use COMPUTED columns:

Now, an index can be created not for all JSON-based COMPUTED columns:

This bug should be fixed in the next version of CTP.

# 9 – ONLINE ALTER COLUMN

The ALTER COLUMN command can now be executed in the ONLINE mode. When you execute ALTER COLUMN, the column data will be available for reading, and blocking the Sch-M schema is applied only at the end of the ALTER operation, when switching to new pages with data (more details can be found here)

# 10 – TRUNCATE TABLE + PARTITIONS

In CTP2.0, the ability to work with separate partitions, not only with the entire table, was added for the TRUNCATE TABLE operation. You can specify not only a separate partition, but the whole range. Let’s create a table with partitions:

A small postscript…

From what I see now, the new version of SQL Server 2016 promises to be very interesting. Each new CTP adds a large number of features, which are difficult to describe in one article. To preserve readability, I left behind Temporal Tables, Dynamic Data Masking and improvements in In-Memory, which I plan to discuss soon.

See also

One Response to “SQL Server 2016 CTP3.1 – What’s New for Developer?”

  1. Leslie Cecile Brown Says:

    Great article! I am working on an executive overview that I need to present to my manager, can anybody point me to some other high-level discussions on this topic, I’m not a great writer (except when it comes to code :)) and I would like to see how some people describe it in a way that’s easy for non-technical folks … Thanks

Leave a Comment