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.
TRUNCATE TABLE with PARTITION
The TRUNCATE TABLE statement now allows the truncation of specific partitions. This can greatly simply maintenance on large partitioned tables. Its 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))
TRUNCATE TABLE dbo.myTable WITH (PARTITIONS (1,5 TO 8));
Note: if the table isnt 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:
- Setup R Services for SQL Server 2016
- SQL Server 2016 R Services: Display R plots in Reporting Services
- the tutorial Getting started with R in SQL Server
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.
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:
- Introducing JSON for SQL Server 2016
- JSON Support in SQL Server 2016
- SQL Server 2016 Advanced JSON Techniques - Part 1
- Advanced JSON Techniques in SQL Server 2016 - Part 2
In earlier versions, FORMATMESSAGE constructed a message from strings located in sys.messages. Now you can also supply your own string:
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.
- 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.
Last Update: 2018-09-12
First Published: 2015-12-09
About the author
View all my tips