T-SQL Enhancements introduced in SQL Server 2016

By: Koen Verbeeck | Updated: 2018-09-12 | Comments (1) | Related: > SQL Server 2016

Problem

SQL Server 2016 introduced several enhancements to the Transact-SQL language used by the database engine. This tip will guide you through the most notable changes and additions.

Solution

TRUNCATE TABLE with PARTITION

The TRUNCATE TABLE statement now allows the truncation of specific partitions. This can greatly simply maintenance on large partitioned tables. It’s also much faster than deleting the rows from a partition using the DELETE statement. The statement takes the following format:

TRUNCATE TABLE [database].[schema].[table] WITH (PARTITIONS [partition number expression] | [range] );

The PARTITIONS argument can be written in the following ways:

Using the number of a partition: (PARTITIONS (8))
Using a comma separated list of partitions numbers: (PARTITIONS (1,2,3,4))
Using a range with the keyword TO: (PARTITIONS (1 TO 4))
Using a combination of the two above: (PARTITIONS (1,2 TO 4))

An example:

TRUNCATE TABLE dbo.myTable WITH (PARTITIONS (1,5 TO 8));

Note: if the table isn’t partitioned, using the PARTITIONS argument will give an error.

DROP IF EXISTS

A new syntax has been introduced to check if an object exists before dropping it. Previously, if you wanted to check if a table existed before you dropped it, you had to write a statement like this:

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestDrop]') AND [type] IN (N'U'))
   DROP TABLE [dbo].[TestDrop];

With the new syntax, this shortens to the following statement:

DROP TABLE IF EXISTS [dbo].[TestDrop];

This statement is much easier to write than the previous lengthier statement.

The new syntax can be used for every object that can be dropped, such as columns, views, indexes, databases, users and so on. In SQL Server 2016 service pack 1, the CREATE OR ALTER syntax was introduced. With this syntax, you can either create a new object or alter if it already exists. You can use this on any object that doesn't require storage, such as triggers, stored procedures and views. This syntax isn't valid for tables, indexes and indexed views. You can find out more about CREATE OR ALTER in the documentation.

Advanced Analytics Extensions

In SQL Server 2016, users can execute scripts written in another language. For the moment, the R language – an important language for data science and advanced analytics - is supported. With the stored procedure sp_execute_external_script users can execute an R script inside SQL Server, leveraging the computational processing power of SQL Server.

This feature is explained in more detail in other tips:

In SQL Server 2017, this functionality was expended to include the language Python and the service was renamed to SQL Server Machine Learning Services.

Querying Temporal Tables

Temporal tables are tables which automatically track history on the row level are introduced in the tip Introduction to SQL Server 2016 Temporal Tables. In order to query the history, new clauses were added for the WHERE clause:

FOR SYSTEM_TIME AS OF 'A'- Returns the version of a row valid on point A in time. If A is on the boundary of two versions, the most recent one is returned.
FOR SYSTEM_TIME FROM 'A' TO 'B' – Returns all versions of a row which were at some point in time valid in the range between A and B. Boundaries are not included.
FOR SYSTEM_TIME BETWEEN 'A' AND 'B' – Returns all versions of a row which were at some point in time valid in the range between A and B. The upper boundary is included.
FOR SYSTEM_TIME CONTAINED IN ('A','B') – Returns all versions of a row which were for its entire duration between the two points in time.

This tip SQL Server 2016 T-SQL Syntax to Query Temporal Tables explains these 4 clauses above in more detail.

In SQL Server 2016 CTP 3.0, a new clause was added:

FOR SYSTEM_TIME ALL – Returns all rows from both the history table and the main table.

JSON support

Similar like XML, SQL Server now supports the JSON format. You can for example convert tabular data to JSON using the FOR JSON clause. An example:

You can also read JSON data and convert it to tabular data by using OPENJSON. There are also built-in support functions:

ISJSON – test a string to see if it contains valid JSON
JSON_VALUE – extract a scalar value from a JSON string
JSON_QUERY – extracts an object or an array from a JSON string

The new JSON functionality has been described in numerous tips:

FORMATMESSAGE

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

Other

There are also other minor additions to the language:

ALTER TABLE can now alter many columns while the table remains online, using WITH (ONLINE = ON | OFF).
Support for Dynamic Data Masking.
The NO_PERFORMANCE_SPOOL query hint is added to prevent spool operators from being added to the query plan.
You can now add the MAXDOP option to DBCC CHECKTABLE, DBCC CHECKDB and DBCC CHECKFILEGROUP to control the degree of parallelism.
Manage session context with sp_set_session_context, the SESSION_CONTEXT function and the CURRENT_TRANSACTION_ID function.

Next Steps

For more information on all of the T-SQL changes, check out What's New in the Database Engine.
For more SQL Server 2016 tips, you can use this overview.
Check out all of the SQL Server 2016 tips.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-09-12

Thursday, February 27, 2020 - 1:37:20 PM - Brian	Back To Top (84820)
You missed "AT TIME ZONE", which is crucially important when you query date/time schemas that you don't control which are missing the time zone.