What's new in the T-SQL language for SQL Server 2016
A quick summary of the (few) new language features introduced into SQL for SQL Server 2016.

This is part of a wide-ranging blog explaining the new features in every part of SQL Server 2016.

Posted by Andy Brown on 23 June 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Changes to the SQL Language for SQL Server 2016

Although the SQL Server database engine has undergone profound changes, these will mostly only be of interest to database administrators.  This blog just considers the few new features in the T-SQL language for SQL programmers.

None of these is quite as momentous as the introduction of the FORMAT function in SQL Server 2012!

Dropping tables (but only if they exist)

Advanced SQL programmers will be used to dropping tables like this:

-- delete a temporary table only if it exists

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL

DROP TABLE #temp

There is a new command in T-SQL for SQL Server 2016 allowing you to drop a table, but only if it exists:

-- delete temp table if it exists

DROP TABLE IF EXISTS #temp

Yeah!  Note that you can also use this when dropping other SQL Server objects.

The FORMATMESSAGE function

This isn't new in SQL Server 2016, but it has been extended to allow you to construct any message.  Here's an example:

-- display owly message

SELECT FORMATMESSAGE('Wise Owl say tu-%s, tu-%s', 'whit', 'whoo')

What this does is to substitute the words whit and whoo in wherever the string calls for a bit of text.  Useful, huh?  Here's a bit of SQL using this to create a series of statements selecting all of the columns from each table in a database:

-- create SQL statements to select columns from every table

SELECT

FORMATMESSAGE('SELECT * FROM %s',t.name)

FROM

Movies.sys.tables AS t

Here's what this would output for the Wise Owl Movies database:

Series of select statements

It's not rocket science, but it might save someone a minute or two ...

 

Apart from these two new statements, there is lots of language support for new features like stretched databases and temporal tables, but I'm betting the average Wise Owl client will neither care about these nor use them!

This blog has 0 threads Add post