T-SQL Enhancements in SQL Server 2016

The relational database engine and T-SQL are the core of SQL Server 2016. T-SQL is used for both SQL Server management as well as stored procedures, triggers, functions and application development. As you might expect with a new release, Microsoft has made many significant enhancements to T-SQL with SQL Server 2016. Here are some of the biggest changes to T-SQL in SQL Server 2016.

DROP IF EXISTS

One of the handiest T-SQL enhancements with SQL Server 2016 is the new DROP IF EXITS statement. It lets you check if an object exists before dropping it enabling you to skip wiring all those IF EXISTS blocks that we all write in out T-SQL scripts. You can see an example below.

DROP TABLE IF EXISTS [dbo].[MyTable];

STRING_SPLIT Function

STRING_SPLIT is another handy new function that makes it easy to parse strings based on a delimiter. The string can be a varchar, nvarchar, char, or nchar data type.

DECLARE @string varchar(100) = 'Richard, Mike, Mark'SELECT value FROM string_split(@string, ',')

TRUNCATE TABLE with PARTITION

The TRUNCATE TABLE statement in SQL Server 2016 supports the truncation of specific partitions which can simplify maintenance on large partitioned tables. The TRANCATE TABLE statement is faster than deleting the rows from partitions using the DELETE statement. You can the enhanced TRANCATE TABLE statement below.

TRUNCATE TABLE dbo.myTable WITH (PARTITIONS (1, 6 TO 8));

FOR SYSTEM_TIME Clause

The FOR SYSTEM_TIME clause was introduced to support the new temporal tables feature. Temporal tables automatically track a table’s history. And the new clauses was added to enable querying a temporal table’s history. The follow example shows how you can use the new FOR SYSTEM_TIME clause.

SELECT * FROM Employee FOR SYSTEM_TIME          BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'    WHERE EmployeeID = 1000 ORDER BY ValidFrom; 

FOR JSON Clause

Likewise, the FOR JSON clause was add to enable you to convert tabular data to JSON. Unlike XML which has a native data type, the new JSON support is enabled by using the NVARCHAR data type. You can see an example of using the new FOR JSON clause in the following listing:

SELECT object_id, name FROM sys.tables FOR JSON PATH

JSON Functions

OPENJON Function

The OPENJSON table-value function (TVF) queries JSON text and can locate an array of JSON objects or iterate through the elements of array and generate a rowset from the results. You can see an example of using the OPENJSON function below.

DECLARE @JSON NVARCHAR(100)SET @json = N'[ null, "string", 1, [true, false], ["a","b","c"], {"obj1":"obj2"} ]'; SELECT * FROM OPENJSON( @json ) 

FORMATMESSAGE Function

The FORMATMESSAGE function as also been enhanced to enable supply your own string values. The previous version constructed a message from strings located in sys.messages. The following listing illustrates the new FOMRMESSAGE capabilities.

SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'variable1', 'variable2') AS Result; 

Stored procedure sp_execute_external_script to execute R scripts

To help enable R support in SQL Server 2016 Microsoft added the sp_Execute_externl_script stored procedure that enables you to execute R scripts from T-SQL running n SQL Server. You can see a simple example of executing R from T-SQL in the following listing.

EXEC sp_execute_external_script  @language =N'R',   @script=N'OutputDataSet<-InputDataSet',    @input_data_1 =N'select 1 as hello'   WITH RESULT SETS (([hello] INT not null));   GO

HPE and Microsoft are the underwriters of this article.