New T-SQL programmability features in Azure SQL Database

New programmability features that are implemented in SQL Server 2016 are also available in Azure SQL Database. From this version, you can use additional built-in functions and statements. The following new features are available:

• AT TIME ZONE clauseEnables you to convert an inputdate to the corresponding datetimeoffset value in the target time zone by using time zone conversion rules, for example:

SELECT getdate() AT TIME ZONE 'Central European Standard Time';

• COMPRESS and DECOMPRESS functionsEnable you to compress content by using the GZip algorithm. The GZip algorithm is compatible with common client libraries and tools (such as the .NET compression library and web browsers). To check the compression rate you can run the following command:

SELECT datalength((select * from sys.objects for xml path)) / 1.0 / datalength(compress((select * from sys.objects for xml path)))

• DATEDIFF_BIGReturns the count (signed big integer) of the specified datepart boundaries crossed between the specified startdate and enddate. The existing DATETIME function cannot return differences with high precision units (for example, it will fail if you try to find the number of milliseconds in a 45 minute timeframe).

-- DATEDIFF function cannot return number of microseconds in a 45 minute timeframe:

SELECT DATEDIFF(microsecond, '2006-12-31 14:00:00.0000000', '2006-12-31 14:45:00.0000000')

-- DATEDIFF_BIG function will return results even for nanoseconds in a one-year timeframe:

SELECT DATEDIFF_BIG(nanosecond, '2005-12-31 14:00:00.0000000', '2006-12-31 14:45:00.0000000')

• DROP IF EXISTSEnables you to conditionally drop the following objects:
  • DATABASE, TABLE, VIEW, INDEX
  • PROCEDURE, FUNCTION, TRIGGER
  • ROLE, RULE, SCHEMA,USER, SECURITY POLICY
  • TYPE, DEFAULT, SEQUENCE, SYNONYM

Some examples of DROP IF EXISTS statements are:

• DROP TABLE IF EXISTS my_tab
• DROP PROCEDURE IF EXISTS my_proc
• ALTER TABLE my_tab DROP CONSTRAINT IF EXISTS CK_VALID_PHONE_NUMBER