BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
FORMATMESSAGE('SELECT * FROM %s',t.name)
Movies.sys.tables AS t
Here's what this would output for the Wise Owl Movies database:
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!