Еще недавно в моей памяти отложился анонс 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, которые планирую добавить в скором продолжении.
ссылка на оригинал статьи http://habrahabr.ru/post/272211/
Добавить комментарий