SQL Server 2016 CTP3.1-開発者向けの新機能

最近まで、Satya Nadellaが個人的に発表したSQL Server 2016の発表は、私の記憶の中で延期されました。 そして突然、彼の頭の上の雪のように、最新のCommunity Technology Previewが次々と登場し始めました（現時点では最新バージョンはCTP3.1です ）。 私は新しいバージョンに慣れてきたので、私はますます私の印象を共有したいと思いました...



次に、 SQL Server 2016の新しい構文機能の概要： JSON 、 GZIP 、 DROP IF EXISTS 、 TRUNCATE TABLE by section、new functions ...

＃1-存在する場合のドロップ

CREATE TABLE dbo.tbl ( a INT, b INT, CONSTRAINT ck CHECK (a > 1), INDEX ix CLUSTERED (a) )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

オブジェクトを削除する前にチェックを行う必要がある場合：

 IF OBJECT_ID(N'dbo.tbl', 'U') IS NOT NULL DROP TABLE dbo.tbl
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

よりコンパクトな構文が登場しました：

 DROP TABLE IF EXISTS dbo.tbl
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

子を削除する機能を追加しました：

 ALTER TABLE dbo.tbl DROP COLUMN IF EXISTS b ALTER TABLE dbo.tbl DROP CONSTRAINT IF EXISTS ck ALTER TABLE dbo.tbl DROP CONSTRAINT IF EXISTS ix
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

1つの親構造オブジェクトに対して、以下を組み合わせることができます。

 ALTER TABLE dbo.tbl DROP COLUMN IF EXISTS b, CONSTRAINT IF EXISTS ck, CONSTRAINT IF EXISTS ix
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

DROP IF EXISTSは、ほぼすべてのオブジェクトでサポートされています（完全なリストはこちらで確認できます ）。

 DROP TABLE IF EXISTS #temp DROP TABLE IF EXISTS ##temp DROP VIEW IF EXISTS dbo.view1 DROP PROCEDURE IF EXISTS dbo.proc1 DROP DATABASE IF EXISTS db
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃2-SESSION_CONTEXT

私の練習では、ユーザーセッション内でパラメーターを共有するためのタスクがありました。 以前は、 CONTEXT_INFOを使用する必要がありましたが、そのサイズは128バイトに制限されていました。

 DECLARE @UserID SMALLINT = 1 , @LocaleID INT = 123 DECLARE @ctn VARBINARY(128) SET @ctn = CAST(@UserID AS BINARY(2)) + CAST(@LocaleID AS BINARY(4)) SET CONTEXT_INFO @ctn
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

セッションごとに256Kbを保存できる新しい関数SESSION_CONTEXTにより、すべてが少し便利になりました。

 EXEC sys.sp_set_session_context @key = N'UserID', @value = 1 EXEC sys.sp_set_session_context @key = N'LocaleID', @value = 123 SELECT UserID = SESSION_CONTEXT(N'UserID') , LocaleID = SESSION_CONTEXT(N'LocaleID')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃3-CHECKDB + MAXDOP

既定では、 DBCC CHECKDBは論理コアの数に等しいスレッドの数を使用します。 サーバー全体のパフォーマンスを低下させないように、スレッドの数を制限できるようになりました。

 DBCC CHECKDB(N'AdventureWorks2016CTP3') WITH MAXDOP = 4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同様の機能がDBCC CHECKTABLEおよびDBCC CHECKFILEGROUPに追加されました。

 USE AdventureWorks2016CTP3 GO DBCC CHECKTABLE('HumanResources.Employee') WITH MAXDOP = 4 DBCC CHECKFILEGROUP(1) WITH MAXDOP = 4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

DBCCの場合、CHECKDBは、 PERSISTEDオプションを使用してフィルター選択されたインデックスとCOMPUTED列をチェックするときに、いくつかの最適化を行いました。 また、多数のセクションを含むテーブルのチェック時間を短縮しました。

＃4-フォーマットメッセージ

以前のバージョンでは、 FORMATMESSAGE関数は以前に追加されたカスタムメッセージのみを使用できました。

 EXEC sys.sp_addmessage @msgnum = 66667, @severity = 16, @msgtext = N'param1: %s, param2: %s' DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(66667, N'one', N'two') SELECT @msg
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、任意のマスクを指定できます。

 SELECT FORMATMESSAGE('val1: %+i, val2: %+d', 5, -6)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

多くのルーチンクォータまたは文字列連結操作をよりエレガントにすることができます。

 SELECT FORMATMESSAGE('SELECT * FROM [%s].[%s]', SCHEMA_NAME([schema_id]), name) FROM sys.objects WHERE [type] = 'U'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃5-圧縮と解凍

新しいエディションには、 GZIPの COMPRESSおよびDECOMPRESSのサポートが組み込まれています。 デコード時には、結果が変換される正しいデータ型を監視することが重要です。

 DECLARE @a VARBINARY(MAX) = COMPRESS('test test test') SELECT @a , DECOMPRESS(@a) , CAST(DECOMPRESS(@a) AS NVARCHAR(MAX)) --  , CAST(DECOMPRESS(@a) AS VARCHAR(MAX))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ANSI文字列をエンコードし、結果の値のデコードを試みます。

 ------------------------------------ ---------------------- ---------------- ----------------- 0x1F8B08000000000004002B492D2E512 0x746573742074657374 整瑳琠獥⁴整瑳 test test test
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃6-DATEDIFF_BIG

SQL Server 2008では、 DATEDIFF関数の新しいパラメーターMICROSECONDおよびNANOSECONDが登場しましたが、日付範囲が大きすぎる場合：

 SELECT DATEDIFF(NANOSECOND, '20000101', '20160101')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これはエラーにつながる可能性があります。

 Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そのような状況のために、新しい関数DATEDIFF_BIGが追加されました ：

 SELECT DATEDIFF_BIG(NANOSECOND, '20000101', '20160101')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃7-タイムゾーンで

CTP3.0には、新しいシステムビューが導入されています。

 SELECT name, current_utc_offset, is_currently_dst FROM sys.time_zone_info
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

タイムゾーンのリストを取得できる場所：

 name current_utc_offset is_currently_dst -------------------------- ------------------ ---------------- Dateline Standard Time -12:00 0 UTC-11 -11:00 0 ... Central Standard Time -06:00 0 ... Pacific SA Standard Time -03:00 0 UTC-02 -02:00 0 ... UTC +00:00 0 GMT Standard Time +00:00 0 Greenwich Standard Time +00:00 0 ... Belarus Standard Time +03:00 0 Russian Standard Time +03:00 0 ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

AT TIME ZONEを使用すると、特定のタイムゾーンの時刻を表示できます。

 SELECT CONVERT(DATETIME2, GETDATE()) AT TIME ZONE N'Belarus Standard Time'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ---------------------------------- 2015-12-02 14:51:02.1366667 +03:00
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

パラメータ化できます：

 DECLARE @tz NVARCHAR(256) = N'Belarus Standard Time' SELECT GETDATE() AT TIME ZONE @tz , CONVERT(DATETIME2, GETDATE()) AT TIME ZONE @tz
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ---------------------------------- 2015-12-02 14:51:28.6266667 +03:00
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そのようなイノベーションの用途は何ですか？ たとえば、現在の時刻に基づいて、他のタイムゾーンの時間を表示できます。

 SELECT name, CONVERT(DATETIME, SWITCHOFFSET(SYSUTCDATETIME() AT TIME ZONE name, DATENAME(TzOffset, SYSDATETIMEOFFSET())) ) FROM sys.time_zone_info
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ---------------------------------- ----------------------- Dateline Standard Time 2015-12-03 02:56:41.940 UTC-11 2015-12-03 01:56:41.940 ... Pacific SA Standard Time 2015-12-02 17:56:41.940 UTC-02 2015-12-02 16:56:41.940 ... UTC 2015-12-02 14:56:41.940 GMT Standard Time 2015-12-02 14:56:41.940 Greenwich Standard Time 2015-12-02 14:56:41.940 Central European Standard Time 2015-12-02 13:56:41.940 ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃8-JSON

JSONサポートは、 SQL Server 2016の主要な機能の1つです。 CTP2.0以降 、 XMLとの類推によってJSONを生成できるようになりました。 2つのFOR JSON AUTOおよびFOR JSON PATHコンストラクトがサポートされています。

 SELECT TOP (2) name, database_id, source_database_id, create_date FROM sys.databases FOR JSON AUTO, ROOT('root')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 {"root": [ {"name":"master","database_id":1,"create_date":"2003-04-08T09:13:36.390"}, {"name":"tempdb","database_id":2,"create_date":"2015-12-02T11:34:36.080"} ] }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT TOP (2) name , [db.id] = database_id , [db.scr_id] = source_database_id , [db.date] = create_date FROM sys.databases FOR JSON PATH, ROOT
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 {"root": [ { "name":"master", "db":{"id":1,"date":"2003-04-08T09:13:36.390"} }, { "name":"tempdb", "db":{"id":2,"date":"2015-12-02T11:34:36.080"} } ] }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

生成時にNULL値がJSONに含まれるようにするには、 INCLUDE_NULL_VALUESオプションを使用する必要があります。

 SELECT TOP (1) name, database_id, source_database_id FROM sys.databases FOR JSON AUTO, INCLUDE_NULL_VALUES
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [ { "name":"master", "database_id":1, "source_database_id":null } ]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JSONから角かっこを削除する必要がある場合は、このためにWITHOUT_ARRAY_WRAPPERオプションが提供されます（バージョンCTP3.2以降で使用可能）。

 SELECT TOP (1) name, database_id, source_database_id FROM sys.databases FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 { "name":"master", "database_id":1 }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JSONを保存するには、 NVARCHARを使用する必要があります。別のデータ型が提供されていないためです。

 DECLARE @json NVARCHAR(MAX) = ( SELECT key1 = 1, key2 = GETDATE() FOR JSON PATH ) SELECT @json
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 {"key1":1,"key2":"2015-12-02T15:45:05.530"}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JSONから選択するには、 OPENJSONを使用できます。 エントリが1つしかない場合、結果は「key-value」の形式で返されます。

 DECLARE @json NVARCHAR(MAX) = N' { "UserID" : 1, "UserName": "JC Denton", "IsActive": true, "RegDate": "2015-12-02" }'; SELECT * FROM OPENJSON(@json)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 key value type ----------- ------------ ---- UserID 1 2 UserName JC Denton 1 IsActive true 3 RegDate 2015-12-02 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この動作は、「別のオプション」分割線として使用できます。

 DECLARE @a NVARCHAR(100) = '1,2,3' SELECT CAST(value AS INT) FROM OPENJSON(N'[' + @a + N']')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ----------- 1 2 3
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

複数のエントリがある場合：

 DECLARE @json NVARCHAR(MAX) = N' [ { "UserID" : 1, "UserName": "JC Denton", "IsActive": true, "RegDate": "2015-12-02" }, { "UserID" : 2, "UserName": "Paul Denton", "IsActive": false, "RegDate": "2015-11-02" } ]'; SELECT * FROM OPENJSON(@json)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果は次のようになります。







通常の形式では、データは次のように取得できます。

 DECLARE @json NVARCHAR(MAX) = N' [ { "UserID" : 1, "UserName": "JC Denton", "IsActive": true, "RegDate": "2015-12-02" }, { "UserID" : 2, "UserName": "Paul Denton", "IsActive": 0, "RegDate": "2015-11-02" } ]'; SELECT * FROM OPENJSON(@json) WITH ( UserID INT, UserName VARCHAR(50), IsActive BIT, [Date] DATE '$.RegDate' )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 UserID UserName IsActive Date ----------- --------------- -------- ---------- 1 JC Denton 1 2015-12-02 2 Paul Denton 0 2015-11-02
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

スカラー式を取得する必要がある場合は、 JSON_VALUEを使用できます。

 DECLARE @json NVARCHAR(4000) = N' { "UserID" : 1, "Detail": [ { "Year":2016 }, { "Year":2015, "Options": [{ "Visible":true }] ] }' SELECT JSON_VALUE(@json, '$.UserID') , JSON_VALUE(@json, '$.Detail[0].Year') , JSON_VALUE(@json, '$.Detail[1].Year') , JSON_VALUE(@json, '$.Detail[1].Options[0].Visible')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JSONからオブジェクトの配列を取得するために、 JSON_QUERY関数が提供されます。

 DECLARE @json NVARCHAR(4000) = N' { "Main" :{ "Detail": { "Name":"color", "Value":"blue" } }' SELECT JSON_QUERY(@json, '$.Main.Detail')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

テキストがJSONであることを確認する必要がある場合は、 ISJSON関数を使用できます 。

 DECLARE @json NVARCHAR(MAX) = N'{"ID" : 1}'; SELECT ISJSON(@json), ISJSON('')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JSONには特別なインデックスはありませんが、 COMPUTED列を使用することは可能です。

 DROP TABLE IF EXISTS dbo.Users CREATE TABLE dbo.Users ( OrderID INT PRIMARY KEY, JSON NVARCHAR(4000), CONSTRAINT CK_IsJSON CHECK (ISJSON(JSON)=1), Age AS (CONVERT(INT, JSON_VALUE(JSON, '$.Age'))) ) CREATE INDEX IX_Age ON dbo.Users(Age)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

すべてのJSONベースのCOMPUTED列にインデックスを作成できるわけではありません。

 ALTER TABLE dbo.Users ADD RegDate AS (CAST(JSON_VALUE(JSON, '$.Age') AS DATE)) GO CREATE INDEX IX_RegDate ON dbo.Users(RegDate)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 Msg 2729, Level 16, State 1, Line 15 Column 'RegDate' in table 'dbo.Users' cannot be used in an index or statistics or as a partition key because it is non-deterministic.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このバグは、 CTPの次のバージョンで修正されるはずです。

＃9-オンラインALTER COLUMN

ALTER COLUMNコマンドをオンラインモードで実行できるようになりました。 コマンドを実行すると、列のデータが読み取り可能になり、新しいデータページへの切り替えが発生したときに、 ALTER操作の最後にのみSch-Mスキームロックが適用されます（詳細については、 こちらをご覧ください ）。

 DROP TABLE IF EXISTS dbo.tbl CREATE TABLE dbo.tbl (x VARCHAR(255) NULL) GO ALTER TABLE dbo.tbl ALTER COLUMN x VARCHAR(255) NOT NULL WITH (ONLINE = ON) GO ALTER TABLE dbo.tbl ALTER COLUMN x NVARCHAR(255) COLLATE Cyrillic_General_100_CI_AS NOT NULL WITH (ONLINE = ON)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃10-切り捨てテーブル+パーティション

CTP2.0では、 TRUNCATE TABLE操作により、テーブル全体だけでなく、個々のセクションを操作する機能が追加されました。 この場合、個別のセクションだけでなく、セクションの範囲全体を指定できます。 テストテーブルを作成します。

 CREATE PARTITION FUNCTION PF (SMALLINT) AS RANGE RIGHT FOR VALUES (1, 2, 3, 4, 5) GO CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]) GO DROP TABLE IF EXISTS dbo.tbl CREATE TABLE dbo.tbl (a SMALLINT PRIMARY KEY) ON PS (a) GO INSERT INTO dbo.tbl (a) VALUES (0), (1), (2), (3), (4), (5) SELECT partition_number, [rows] FROM sys.partitions WHERE [object_id] = OBJECT_ID('dbo.tbl') AND index_id < 2
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ---------------- ------ 1 1 2 1 3 1 4 1 5 1 6 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 TRUNCATE TABLE dbo.tbl WITH (PARTITIONS (1, 4 TO 5))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 partition_number rows ---------------- ------ 1 0 2 1 3 1 4 0 5 0 6 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

＃11-CURRENT_TRANSACTION_ID

CTP3.0では、名前に基づいて現在のトランザクションを返す新しい関数CURRENT_TRANSACTION_IDが追加されました。おそらく、この関数は誰かに役立つでしょうが、より明確な例を思い付くことができませんでした。

 BEGIN TRANSACTION UPDATE HumanResources.Employee SET SalariedFlag = 0 WHERE BusinessEntityID = 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT ec.session_id, s.[text] FROM sys.dm_tran_session_transactions t JOIN sys.dm_exec_connections ec ON t.session_id = ec.session_id CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) s WHERE t.transaction_id != CURRENT_TRANSACTION_ID()
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 session_id text ----------- --------------------------- 67 BEGIN TRANSACTION UPDATE HumanResources.Employee SET SalariedFlag = 0 WHERE BusinessEntityID = 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

あとがき...



私が今見ているものから、 SQL Server 2016のリリースは非常に興味深いものになると約束しています。 新しいCTPごとに、多数のチップが追加されますが、これは1つの記事で説明するのは困難です。 読みやすくするために、 一時テーブル 、 動的データマスキング、およびインメモリの改善を残しました。これらはすぐに追加する予定です。



この記事を英語圏の聴衆と共有したい場合：

SQL Server 2016 CTP3.1-開発者向けの新機能