SQL Server 2016: T-SQL Enhancements

Author by Concurrency Blog

When a new version of SQL Server is released, I look forward to seeing what new T-SQL commands have been added. There are, hopefully, features that will make the database developer’s job easier, and their code run faster. SQL Server 21016 introduced a few new commands that do both.

DROP IF

There are a lot of reasons you may want to search for the existence of a table, view, or function, and delete it if it’s already in the database. Perhaps you’re running a troubleshooting script that will clear out an older version of a table; maybe you’re testing a new feature in your application. You now have the ability to issue the command:

DROP [Object] IF EXISTS [name];

This syntax will work on a wide variety of objects – tables, views, indexes, procedures, functions, users, and more. (Look for the IF EXISTS option on the MSDN documentation page – such as DROP TABLE.)

In this example, I’m going to show that I have a table named TestJes (but not JesTest).

I can issue a DROP … IF EXISTS command so I don’t have to do conditional logic to figure that out.

Attempting to drop the table that doesn’t exists results in no error message.

SERVERPROPERTY additions

There are over thirty properties you can get information about from SERVERPROPERTY – the edition, the instance name, if it’s part of a failover cluster. 2016 introduces 8 more properties that can be returned:

  • InstanceDefaultDataPath
  • InstanceDefaultLogPath
  • ProductBuild
  • ProductBuildType
  • ProductMajorVersion
  • ProductMinorVersion
  • ProductUpdateLevel
  • ProductUpdateReference

If you’re running 2012 or 2014, and are updating your server, you can see some of these properties, too. Which properties you can see on which versions is on the documentation page.

The benefit to having these exposed via T-SQL is that it will enable you to gather more information programmatically – I’ll be able to update scripts I use to gather SQL Server information in a new environment, for example.

SELECT 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS, 
SERVERPROPERTY('MachineName') AS MachineName, 
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('Edition') AS Edition, 
SERVERPROPERTY('EngineEdition') AS EngineEdition, 
SERVERPROPERTY('ProductVersion') AS ProductVersion, 
SERVERPROPERTY('IsClustered') AS IsClustered, 
SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled, 
--The below are 2016 or updated 2012/2014 
SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath, 
SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath, 
SERVERPROPERTY('ProductBuild') AS ProductBuild, 
SERVERPROPERTY('ProductBuildType') AS ProductBuildType, 
SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion, 
SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion, 
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel, 
SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference

On a SQL Server 2014 instance:

serverproperty-2014.PNG

On a SQL Server 2016 instance:

serverproperty-2016.PNG

STRING_SPLIT

This is my favorite new T-SQL feature! There is now a built-in way to split string values, based on a delimiter.

The syntax is STRING_SPLIT ( string , delimiter ). The string passed in can be varchar, nvarchar, char, or nchar. The delimiter is a single character – a comma, a space, a dash.

A very simple example:

string_split-simple.PNG

An example showing how to use delimited data in a table:

CREATE TABLE #StringSplit
(ID INT IDENTITY,
String VARCHAR(250));
GO

INSERT INTO #StringSplit
VALUES ('red - green - pink - blue'), ('blue - yellow - green'), ('orange - brown - black - white - yellow');
GO

SELECT ID, String
FROM #StringSplit;

SELECT ID, String, Value
FROM #StringSplit
       CROSS APPLY string_split(String, '-');

DROP TABLE #StringSplit;
GO

string_split-table.PNG

For more information on performance, read Aaron Bertrand’s excellent blog, Performance Surprises and Assumptions : STRING_SPLIT().

There’s More!

This is only a sampling of the T-SQL enhancements introduced – check out the full list at https://msdn.microsoft.com/en-us/library/bb510411.aspx#Anchor_1!

Author

Concurrency Blog

The latest about Concurrency