SQL Server 2016 CTP3.1 — что нового для разработчика?
Еще недавно в моей памяти отложился анонс SQL Server 2016, которую лично презентовал Сатья Наделла. И вдруг, как снег на голову, стали одна за одной выходить свежие Community Technology Preview (на данный момент самая свежая версия — CTP3.1). По мере знакомства с новой версией, все больше хотелось поделиться впечатлениями…
Далее обзор новых синтаксических фишек нового SQL Server 2016.
#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')
Можно использовать функцию 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
Небольшое послесловие…
Из того, что я сейчас наблюдаю, релиз SQL Server 2016 обещает быть очень интересным. С каждым новым CTP добавляется большое количество фишек, которые сложно описать в рамках одной статьи. Чтобы сохранить читабельность, за бортом этого обзора я оставил Temporal Tables, Dynamic Data Masking и улучшения в In-Memory, которые планирую добавить в скором продолжении.
