New T-SQL programmability features in Azure SQL Database

2016年1月23日

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 clause Enables 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 functions Enable 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_BIG Returns 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 EXISTS Enables 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

免费帐户

免费获得 $200 点 Azure 信用额度和 12 个月的常见服务使用权

免费开始

Visual Studio

订阅者每年可获得高达 $1800 的 Azure 服务

立即激活

初创企业

加入 BizSpark 计划并获得免费的 Azure 服务

了解更多