This page is intended as a quick summary of T-SQL techniques new to SQL Server 2016 that Data Services might decide to adopt as best practice. It also mentions SQL 2012 enhancements that we could make more use of.
DROP IF EXISTS
This new syntax can be used for every object that can be dropped, such as columns, views, indexes, databases, users and so on.
use DataMart;
drop table if exists #StudentMajors
select *
into #StudentMajors
from dbo.StudentMajor sm
where sm.TermId = 20193
and sm.RecordType = 'C'
JSON Support
Over the past few years, more and more of the REST Web services that we use provide data in JSON rather than XML (or give the option for either). Starting with SQL Server 2016, we can parse, query, create, and generally interact with JSON directly in T-SQL. An example looks like:
use DataMart;
declare @StudentMajors varchar(max)
set @StudentMajors = (
select top 3 sm.StudentId as 'Students.StudentId'
, sm.TermId as 'Students.TermId'
, sm.MajorCode as 'Students.MajorCode'
from dbo.StudentMajor sm
where sm.TermId = 20193
and sm.RecordType = 'C'
for json path
)
print @StudentMajors
There's a terrific article with code examples on the Microsoft Docs site here: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server
TRY_CONVERT (PARSE and TRY_PARSE, too)
When the long-used CONVERT function fails, we get an error and our script stops. Not with TRY_CONVERT:
select try_convert(int, 55) as GoodConversion, try_convert(int, 'Ben') as BadConversion
CHOOSE and IIF
Basically, we now have a syntactically cleaner way of choosing one of several options inline:
select choose(1, 'a', 'b', 'c') as FirstFromList, choose(2, 'a', 'b', 'c') as SecondFromList
select iif(1 = 2, 'a', 'b') as Result