Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

T-SQL Enhancements in SQL Server 2016


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

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.

Solution

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. 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.

Drop if exists

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.

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:

FOR JSON

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:

FORMATMESSAGE

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools