The Right Database Monitoring Tools Make All the Difference
Tuesday, June 26, 2018 - click here to learn more and to register
SQL Server 2016 which is currently still in preview at the time of writing introduces several enhancements to the Transact-SQL language used by the database engine. This tip will guide you through the most notable changes and additions.
SQL Server 2016 preview
As mentioned earlier, at the time of writing SQL Server 2016 is still in preview (currently CTP 3.0 has been released). This means functionality or features of the T-SQL language might change, disappear or be added in the final release.
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. For the moment, the IF EXISTS is only added for the DROP statement. It would be a great addition if it would also work with CREATE (but as IF NOT EXISTS) and ALTER. Maybe in another preview?
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 will be explained in more detail in future tips.
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: 2015-12-09
About the author
View all my tips