DROP IF EXISTS
This allows us to drop objects without first having to check if they exist. It works on pretty much every object you could want to drop and changes this…
Allows a string to be split on a character.
This takes an input string and a type and will escape the characters depending on the type specified. In this release the only supported type is JSON.
A number of functions for working JSON have been introduced including…
- FOR JSON PATH
I have covered these in more detail in my post Using JSON In SQL Server 2016
Temporal Table Support
Temporal tables allow us to query a table and get back the data as it was at a given point in table. SQL Server will automatically manage the history of a table that we declare as Temporal. We can then work with this data using the new FOR_SYSTEM_TIME syntax.
For more information see SQL 2016 Temporal Tables By Example
TRUNCATE TABLE WITH PARTITION
The TRUNCATE TABLE statement has been improved to allow you to truncate by partition on partitioned tables. If no partitions are specified it will truncate all of them. To specify partitions you can list them one by one or pass in a range or a mixture of both…
FORMATMESSAGE can now take a string as input rather than just working with existing messages in sys.messages. Previously you had to pass an Id in from sys.messages like this…
With the new changes we can specify the message string when we call FORMATMESSAGE