Еще недавно в моей памяти отложился анонс SQL Server 2016, которую лично презентовал Сатья Наделла. И вдруг, как снег на голову, стали одна за одной выходить свежие Community Technology Preview (на данный момент самая свежая версия – CTP3.1). По мере знакомства с новой версией, все больше хотелось поделиться впечатлениями…

Далее обзор новых синтаксических фишек SQL Server 2016: JSON, GZIP, DROP IF EXISTS, TRUNCATE TABLE по секциям, новые функции…

#1 – DROP IF EXISTS

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

Для одного родительского объекта конструкции можно объединять:

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

Теперь все стало чуточку удобнее за счет новой функции SESSION_CONTEXT в которой разрешили хранить 256Кб на сессию:

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 сделали некоторые оптимизации при проверке фильтрованных индексов и COMPUTED столбцов с опцией PERSISTED. Еще сократили время проверки для таблиц, которые содержат большое количество секций.

#4 – FORMATMESSAGE

В предыдущих версия функция 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 – COMPRESS & DECOMPRESS

В новой редакции появилась встроенная поддержка 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 строку и пытаемся декодировать полученное значение:

----------------------------------------------------------- -------------------------------- ---------------- -----------------
0x1F8B08000000000004002B492D2E5128811100026A5B230E000000    0x7465737420746573742074657374   ???????     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 – AT TIME ZONE

В 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. Начиная с CTP2.0 появилась возможность генерировать JSON по аналогии с XML. Поддерживаются две конструкции 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 необходимо использовать 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. Если запись одна, то возвращается результат в виде «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')

Можно использовать функцию ISJSON, если нужно удостовериться что текст является JSON:

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)

Сейчас не для всех COMPUTED столбцов на основе JSON можно создать индекс:

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 – ONLINE ALTER COLUMN

Команду ALTER COLUMN теперь можно проводить в режиме ONLINE. При выполнении команды данные по столбцу будут доступны для чтения, а блокировка схемы Sch-M накладывается лишь в самом конце операции ALTER, когда происходит переключение на новые страницы с данными (более детально можно прочитать тут).

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 – TRUNCATE TABLE + PARTITIONS

В версии 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 добавляется большое количество фишек, которые сложно описать в рамках одной статьи. Чтобы сохранить читабельность, за бортом этого обзора я оставил Temporal Tables, Dynamic Data Masking и улучшения в In-Memory, которые планирую добавить в скором продолжении.

Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:
SQL Server 2016 CTP3.1 – What’s New for Developer?