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

サブスクライバーは 1 年ごとに最大 $1800 までの Azure サービスが利用できます。

今すぐアクティブ化

スタートアップ

BizSpark プログラムに参加して、Azure サービスを無料で利用しましょう

詳細情報