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

Next Webcast - The Cloud won't fix that (click for more info)
 

T-SQL Enhancements introduced in SQL Server 2016


By:   |   Read Comments   |   Related Tips: More > 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.

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

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:

First Published: 2015-12-09


next webcast 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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools