Just wanted to run through a few more T-SQL additions added to SQL Server 2016. I’m using version CTP 3.1.
DROP IF EXISTS:
The feature I really like is the DROP IF EXISTS statement. Currently, we have to check for the existence of an item before we can attempt to drop it, since trying to drop an object that doesn’t exist will throw an error. Instead we can simply run:
DROP TABLE IF EXISTS dbo.TestTable;
and this code will drop the table if it exists, and run without an exception if the table doesn’t exist.
DIE – Technet SQL Server Blog
AT TIME ZONE:
Using AT TIME ZONE gives a way to convert a datetime value to a datetimeoffset value for a specific time zone by using a time zone name.
declare @Now datetime = getdate(); select @Now as CurrentTime, @Now at time zone 'Eastern Standard Time' as TimeWithOffset;
This returns a datetimeoffset value. The offset from UTC will change periodically through the year, depending if the time zone is in daylight savings time or not. So this doesn’t require us to know the current offset, AT TIME ZONE will take care of the conversion.
We can query the sys.time_zone_info to find the possible time zone values. The table also gives us to current UTC offset as well letting us know if the time zone is currently in daylight savings time.
MSDN – AT TIME ZONE
This isn’t new, but I hadn’t worked much with the datetime offset, I was curious how to convert a datetimeoffset value to a regular datetime.
declare @a datetimeoffset = '2016-01-02 13:00:00.000 -05:00'; select convert(datetime2, @a, 1); select SWITCHOFFSET(@a, '+00:00');
A simple convert will work, or we can use the SWITCHOFFSET function to set no offset for UTC. It returns a datetimeoffset value, but with a 0 offset. We would still need to CAST or CONVERT to get a datetime. SWITCHOFFSET looks to be meant for setting an offset value to a different time zone, so it probably isn’t the best choice to use in this situation.
COMPRESS/DECOMPRESS:
SQL Server 2016 also includes built-in support for compression and decompression, using GZip.
drop table if exists dbo.TestCompress; create table dbo.TestCompress( RecordId int not null identity(1,1), CompressedData varbinary(max) not null ); declare @Text nvarchar(max) = '2015-12-28 Atlanta Falcons 20 Carolina Panthers 13'; insert into dbo.TestCompress(CompressedData) values (COMPRESS(@Text)); select * from dbo.TestCompress; select RecordId, CAST(DECOMPRESS(CompressedData) as nvarchar(max)) from dbo.TestCompress;
This example uses a short text sample, so I wouldn’t expect dramatic (if any) compression gains, but with a larger text or with a BLOB file, we should see benefits.
We store the data in a varbinary(max) column and use COMPRESS to insert the value, and DECOMPRESS to extract it. If we’re dealing with text, we would also need to cast the value back to text to view it.
FORMATMESSAGE:
Currently FORMATMESSAGE is used for error messages that come from sys.messages, so that error information can be inserted into the standard error messages.
We can now supply our own message using %s as place holder and then drop in our text in those placeholders.
declare @Team1 varchar(20) = 'Atlanta Falcons';
declare @Team2 varchar(20) = 'New Orleans Saints';
select FORMATMESSAGE('2016-01-03 %s vs %s', @Team1, @Team2);
SQL Rob 