Tag Archives: SQL Server 2016

SQL Server 2016: STRING_SPLIT, описание и сравнение производительности

Свершилось! Наконец-то в SQL Server 2016 появляется встроенная функция для разбивки строк. Пожалуй, это одна из самых распространённых функций, которые, пожалуй, присутствуют на каждом SQL Server, но до текущей версии были реализованы своими силами. Это та функция, для которой существует множество самых различных реализаций, и, зачастую, не самых быстрых. Давайте поближе взглянем на возможности функции STRING_SPLIT, ее ограничения и сравним ее по скорости с самыми распространенными решениями.

Функция принимает всего 2 параметра: собственно, саму строку, которую необходимо разбить и разделитель. Существует одно очень важное и не очень понятное ограничение, разделитель может быть только длиной один символ. Поэтому, если вам требуется разбить строку по большему разделителю, то эта функция для вас не подойдет, и вам нужно искать другое решение.

На текущий момент одним из самых быстрых решений по разбивки строк считается CLR функция, написанная Adam Machanic (ссылка на статью). Также я решил привести пару других примеров функции, реализация в лоб, а также через inline функцию с соединением с таблицей чисел. Последний из этих вариантов на строках небольшой длины обычно показывает неплохие результаты по сравнению с реализацией в лоб. Ну и сравним скорость со стандартной функцией, входящей в SQL Server 2016. Для начала подготовим таблицу с числами и добавим 2 вышеуказанные функции. Как скомпилировать и добавить CLR функцию, я не привожу, т.к. надеюсь, каждый сможет это сделать самостоятельно.

use test;
go
 
if object_id('dbo.num', 'U') is not null
	drop table dbo.num;
go
 
create table dbo.num (
	n int primary key clustered
);
go
 
insert into dbo.num(n)
select top (1000000) row_number() over (order by (select 1)) as rn
from sys.all_columns as t1
cross apply sys.all_columns as t2
go
 
if object_id('dbo.fn_split', N'TF') is not null
	drop function dbo.fn_split;
go
 
create function dbo.fn_split
(
    @str nvarchar(max),
	@del nchar(1)
)
returns @returntable table 
(
	st nvarchar(4000)
)
as
begin
 
	while charindex(@del, @str) > 0
	begin
		insert into @returntable select substring(@str, 1, charindex(@del, @str) - 1)
		set @str = substring(@str, charindex(@del, @str) + 1, len(@str))
	end
	if @str <> ''
		insert into @returntable values(@str)
 
	return
end
go
 
if object_id('dbo.fn_split2', 'if') is not null
	drop function dbo.fn_split2;
go
 
create function dbo.fn_split2 (
	@str nvarchar(max),
	@del nchar(1) = ','
) returns table
as
return
	select
		substring(@str, n, case when charindex(@del, @str, n) = 0 then len(@str) else charindex(@del, @str, n) - n end) as st
	from dbo.num
	where
		n <= len(@str)
		and (n = 1 or substring(@str, n - 1, 1) = @del)
go

И, собственно, сам код для теста, в котором я сначала подготавливаю текст из 20 тысяч слов разделенных запятой, а потом проверяю его разбивку различными методами.

set statistics time on
 
declare @str varchar(max) = (select top (20000) t1.name + ',' as 'text()'
from sys.all_columns as t1
cross apply sys.all_columns as t2
for xml path(''))
 
set @str = substring(@str, 1, len(@str) - 1)
 
select *
from dbo.split_string_clr(@str, ',');
 
select *
from dbo.fn_split(@str, ',') as f;
 
select *
from dbo.fn_split2(@str, ',') as f;
 
select *
from string_split(@str, ',');

Ну и приведу один из полученных результатов.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 113 ms.

SQL Server Execution Times:
CPU time = 13468 ms, elapsed time = 13759 ms.

SQL Server Execution Times:
CPU time = 143875 ms, elapsed time = 143989 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 216 ms.

Как мы видим, CLR функция и STRING_SPLIT оставляют далеко позади остальные решения, а вот выбрать, кто из них быстрее становится проблематично, т.к. их время запуска практически не отличается и варьируется от теста к тесту. В данном случае расходы на CPU одинаковые, а время выполнения медленнее. Однако, в каких-то запусках, время выполнения становится сравнимым, поэтому я не берусь отдать пальму первенства ни одной из них. Это действительно круто, что наконец-то появляется встроенное решение с превосходной производительностью. Но, если вам потребуется использовать разделитель больше чем в один символ, то рекомендую пользоваться CLR решением.

SQLSaturday в Краснодаре 4 июня 2016 г.

sqlsat503_webОсталось чуть меньше двух недель до, пожалуй, главного события на юге России, посвященного SQL Server – это, уже ставшая традиционной, конференция SQLSaturday. Если честно, я уже сбился со счета, какой год подряд она уже проходит в Краснодаре, но факт, что уже точно больше, чем в Москве.

Вы еще можете успеть зарегистрироваться, купить билеты в Краснодар, посетить эту замечательную бесплатную конференцию, а также провести выходные в этом довольно милом городе.

Официальный сайт конференции доступен по следующей ссылке. Там же вы сможете зарегистрироваться. Ознакомиться с полным списком докладов и докладчиков вы можете на этой странице. Докладов немного, но они достаточно жесткие и интересные, на мой взгляд.

Сам я выступлю с двумя докладами: по языку R в SQL Server 2016, а также с новым, улучшенным докладом про нетрадиционные и провокационные методы оптимизации запросов. Надо отметить, что этот доклад я в ближайшем будущем больше нигде читать не планирую.

Буду рад всех увидеть на своих докладах в Краснодаре. Удачи вам и приятного посещения конференции!

SQL Server 2016: JSON, часть 3, преобразование в реляционные данные

Это статья является продолжением серии про работу с JSON в SQL Server 2016. В предыдущей части я показал, как можно из реляционных данных сгенерировать JSON. В этой мы рассмотрим обратную операцию, как JSON преобразовать в реляционную структуру. Опять же, если вы уже когда-либо работали с XML в SQL Server, то здесь будет похожий принцип: для этой операции используется функция OPENJSON.

Сразу стоит отметить, что OPENJSON будет работать только в базах данных с уровнем совместимости 130.

Существует 2 режима работы функции OPENSON. Самый простой способ – это без указания схемы для результирующей выборки.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json);

В данном случае результат будет представлен в виде трех столбцов: key, value и type. Соответственно в первом будет выведено название свойства, во втором его значение, а в последнем – тип. Т.к. JSON в приведенном выше примере иерархический, да еще и с массивом для одного из свойств, то функция OPENJSON просто вытащила все свойства с первого уровня и вывела их в виде списка. Мы также можем вытащить отдельно все элементы массива или же свойства с нужного нам уровня документа указав необходимый путь.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json, '$.skills');
select * from openjson(@json, 'strict$."additional info"');

Данные в столбце type могут принимать следующие значения.

Значение Тип данных JSON
0 null
1 string
2 int
3 true/false
4 array
5 object

Во втором режиме работы функции OPENJSON вы можете сами описать, как будет выглядеть возвращаемый результат: названия столбцов, их количество, откуда из JSON брать для них значения.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

Если же у вас в JSON документе несколько объектов, то для каждого из них будет сгенерирована отдельная строка, а значения столбцов выбраны по соответствующим путям для каждого из этих объектов.

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}},
	{"first name":"John","last name":"Smith","age":18,"skills":["SQL Server 2014","In-Memory OLTP"],"additional info":{"data2":4}}
]';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

Вот и все про то, как можно выбрать данные из JSON объекта и представить их в виде реляционной структуры. В следующей статье я опишу, как можно изменять JSON документы.

SQL Server 2016: JSON, часть 2, формирование данных

Это статья является продолжением серии про работу с JSON в SQL Server 2016. В предыдущей части я рассказал о том, как можно извлекать данные, а сейчас мы поговорим о том, как можно из реляционных данных сгенерировать JSON. Если вы уже когда-либо работали с XML в SQL Server, то здесь почти то же самое. Для формирования можно выражение FOR JSON.

Самый простой вариант – это использовать FOR JSON AUTO. В этом случае будет сгенерирован массив JSON объектов, где каждая строка в выборке будет отдельным объектом, а колонка свойством.

use tempdb;
go
 
drop table if exists dbo.test_table;
go
 
create table dbo.test_table (
	id int not null,
	name varchar(100) null,
	dt datetime null
);
go
 
insert into dbo.test_table (id, name, dt)
values
	(1, 'qwe', '19000101'),
	(2, 'asd', null),
	(3, null, '20000101');
go
 
select id, name, dt
from dbo.test_table
for json auto;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

Как мы видим, null значения игнорируются. Если мы хотим их включать в JSON, то можем воспользоваться дополнительной опцией INCLUDE_NULL_VALUES.

select id, name, dt
from dbo.test_table
for json auto, include_null_values;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”,”dt”:null},

{“id”:3,”name”:null,”dt”:”2000-01-01T00:00:00″}]

Опция WITHOUT_ARRAY_WRAPPER поможет, если мы хотим вывести JSON без квадратных скобок.

select id, name, dt
from dbo.test_table
for json auto, without_array_wrapper;
go

{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}

Если же мы хотим объединить результаты с корневым элементом, то необходимо воспользоваться опцией ROOT и передать ей нужное название.

select id, name, dt
from dbo.test_table
for json auto, root('root');
go

{“root”:

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

}

Ну и наконец, если мы хотим создать JSON с более сложной структурой, самим присвоить нужные название свойствам, сгруппировать их, то необходимо использовать выражение FOR JSON PATH. Я рекомендую обратиться к первой части цикла статей про JSON, чтобы узнать поподробнее про path выражения для JSON. Ниже я приведу просто пример запроса, который будет генерировать JSON с более сложной структурой.

select
	id,
	name as 'data.full_name',
	dt as 'data.add date'
from dbo.test_table
for json path;
go

[{“id”:1,”data”:{“full_name”:”qwe”,”add date”:”1900-01-01T00:00:00″}},

{“id”:2,”data”:{“full_name”:”asd”}},

{“id”:3,”data”:{“add date”:”2000-01-01T00:00:00″}}]

Ну вот и все про формирование JSON из реляционный данных. В следующей статье мы рассмотрим обратную операцию, как можно преобразовать JSON объект в реляционный набор данных.

Виртуальный Форум – «Данные. Технологии. SQL Server 2016»

Компания Майкрософт организует Виртуальный Форум Microsoft «Данные. Технологии. SQL Server 2016», который состоится 8 июня 2016 года. Форум приурочен к выходу новой версии платформы управления данными Microsoft на базе SQL Server 2016.

Успех цифровой трансформации бизнеса сегодня напрямую зависит от способности организации использовать передовые технологии и инструменты по работе с данными, которые становятся важнейшим ресурсом развития бизнеса – сравнимым, пожалуй, с ролью электричества в эпоху трансформации XIX века. В рамках форума мы обсудим, какие требования сегодня предъявляются к современным платформам по работе с данными, какие ключевые технологии must have в арсенале любой компании.

Основная программа форума состоит из трех паралельных технологических сесссий:

  • SQL Server 2016: новые стандарты в мире транзакции;
  • Бизнес-aналитика: SQL, Power BI, R, Mobile;
  • Azure: новое поколение решений для аналитики, Big Data & IoT.

Подробная программа доступна на сайте мероприятия.

Пространство Виртуальном форума орагизовано таким образом, что бы сможете почуствовать себя участником реальной конференции.
Каждый участник сможет посетить выставку партнерских решений.
Пообщаться с технологическими экспертами MVP, задать интересующие вопросы.
Задать интересующие вопросы докладчикам.
Получить все необходимые материалы и презентации.
И даже выйграть призы – самые активные получат один из 30 сертификатов на сдачу экзаменов по SQL Server 2016.

И это еще не все — увидеть технологии в действии Вы сможете в течение дня, приняв участие в тест-драйве платформы с помощью онлайн лабораторных работ под руководством тренера.

Участие в форуме бесплатное. Необходима предварительная регистрация.

SQL Server 2016: JSON, часть 1, извлечение данных

JSON сейчас является одним из самых широко распространённых форматов обмена данными. Он используется как формат хранения в нескольких NoSQL решениях, в частности, в Microsoft Azure DocumentDB. На мой взгляд, сегодня, он стал даже популярнее XML. Одной из причин, почему так сложилось, является более простая форма и читаемость по сравнению с XML. Поэтому назрела острая необходимость иметь возможность обрабатывать данные в этом формате внутри SQL Server. Поэтому в версии SQL Server 2016 появляется такая возможность.

В первую очередь стоит отметить, что нет отдельного типа для хранения JSON, в отличие от того же XML. Поэтому JSON необходимо хранить в обычных переменных или полях типов varchar или nvarchar.

Для того, чтобы извлечь данных из JSON в SQL Server 2016 добавлены 3 функции: ISJSON, JSON_VALUE, JSON_QUERY.

Первая функция позволяет просто проверить, содержит ли предложенный текст правильный JSON формат, что может быть достаточно удобно перед тем как начать с ним работать.

declare
	@json1 varchar(max) = N'{"test": 1}',
	@json2 varchar(max) = N'1',
	@json3 varchar(max) = null;
 
select
	isjson(@json1) as json1,
	isjson(@json2) as json2,
	isjson(@json3) as json3;

Функция возвращает 1, если это JSON, 0 – если нет и null, если в нее был передан null.

Для того, чтобы извлечь конкретное значение, можно воспользоваться функцией JSON_VALUE.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_value(@json, '$.info.specialization') as [specialization],
	json_value(@json, '$.info."course number"') as [course_number],
	json_value(@json, '$.info.address.town') as [town],
	json_value(@json, '$.info.parents[0]') as [mother],
	json_value(@json, '$.info.parents[1]') as [father];

Ну и наконец, извлечь какой-то фрагмент из JSON можно с помощью функции JSON_QUERY.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_query(@json, '$.info.address') as [address],
	json_query(@json, '$.info.parents') as [parents];

И еще хотелось бы сразу рассказать поподробнее про выражения пути в JSON, которые используются в функциях JSON_VALUE и JSON_QUERY. Как мы уже успели убедиться, они достаточно простые.

$ – ссылка на объект JSON в тексте

$.property1 – ссылка на свойство property1

$.array1[0] – ссылка на первый элемент массива array1 (нумерация начинается с нуля, как в JavaScript)

$.property1.property2.property3 – ссылка на свойство property3, которое является вложенными в property2 и property1. Так извлекаются объекты на нескольких уровнях вложенности.

$.”property name 1″ – если имя свойства содержит специальные символы типа пробелов, доллара и т.п., но его имя должно быть заключено в кавычки.

Также существует 2 типа таких выражений: lax и strict. По умолчанию используется lax, ничего дополнительно указывать не надо, но вы можете сделать это принудительно, указав это ключевое слово впереди выражения, например “lax$.property1” или “strict$.property1”. Отличие этих типов заключается в том, что если вы укажете несуществующие или неправильные пути для функций, то в случае lax выражения вы получите NULL, а в случае strict ошибку. Например, если вы укажете выражение, которое возвращает не скалярное значение для функции JSON_VALUE, lax выражение вернет NULL, а при использовании strict вы получите ошибку.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_value(@json, '$.property.not.exists') as [not exists], -- Свойство не существует
	json_value(@json, 'lax$.property.not.exists') as [not exists], -- Свойство не существует
	json_value(@json, '$.info.address[0]') as [address_0] -- Попытка обратиться к элементу, который не является массивом
;

В случае же использования типа strict мы получим ошибку.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select json_value(@json, 'strict$.property.not.exists') as [not exists]; -- Свойство не существует

Msg 13608, Level 16, State 5, Line 16
Property cannot be found on the specified JSON path.

Стоит также отметить, что все эти функции могут работать не только с переменными, но со столбцами таблицы.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
if object_id('tempdb..#test', 'U') is not null
	drop table #test;
 
create table #test (
	json_text varchar(max) null
);
 
insert into #test (json_text) values (@json);
 
select json_value(json_text, 'strict$.info.parents[0]') as [parents]
from #test;

Ну вот и все про извлечение данных из JSON. В следующей статье будет рассмотрено, как можно сформировать JSON из уже имеющихся реляционных данных.

SQL Server 2016: SESSION_CONTEXT

Немногие знают, но в SQL Server присутствует механизм, когда можно сохранять определенное состояние сессии и передавать сигналы между разными запросами в пределах одной сессии, либо использовать это как дополнительный мониторинг. Ранее я уже писал об этом. Вы можете обратиться к этой статье, чтобы прочитать об этой возможности более подробно. В этой статье я бы хотел рассказать об улучшении этого механизма, особенно, когда нужно было хранить несколько значений, т.к. появляется полноценное хранилище типа key-value.

Для установки значения теперь есть новая хранимая процедура sp_set_session_context. В качестве параметров вы передаете в нее @key – название ключа типа sysname, размером до 128 байт. Также вы передаете параметр @value – значение этого ключа типа sql_variant, размером до 8000 байт. Если вы передадите в параметр @value значение null, то это освободит ранее занимаемую этим значением память. Также имеется опциональный параметр @read_only, который по умолчанию имеет значение 0. Если вы передадите в него 1, то не сможете повторно изменить значение данного параметра. В зависимости от ситуации, это может вам пригодиться.

Ниже я привожу пример установки пары значений для сессии.

exec sp_set_session_context 'rows_per_page', 1000;
exec sp_set_session_context 'user_type', 'simple', @read_only = 1;

Приложение при соединении с базой данных может установить эти настройки, в зависимости от которых будут дальше работать различные хранимые процедуры. В данном случае, в зависимости от типа пользователя возвращать разный набор полей в отчете, а также максимальное количество строк на одну страницу. Это всего лишь один из примеров, как вы можете использовать переменные сессии.

Чтобы в следующем запросе в рамках этой сессии получить значение какого-либо параметра, нужно воспользоваться функцией SESSION_CONTEXT.

select session_context(N'user_type');

А дальше вы уже можете использовать его по своему усмотрению.

Каждый пользователь может устанавливать и изменять настройки в рамках своей сессии, но есть ограничение в 256 кб на одну сессию (согласно документации). Если оно будет превышено, возникнет ошибка. Одна тест показывает, что ошибка возникает, если размер превышает 1 Мб. Так что, скорее всего, размер был расширен, но документация пока не обновлена.

Msg 15665, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 0]
The value was not set for key ‘125’ because the total size of keys and values in the session context would exceed the 1 MB limit.

Также с помощью системного представления sys.dm_os_memory_objects вы можете посмотреть, сколько всего памяти занято на вашем сервере переменными сессий.

select *
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SESSION_CONTEXT';

Как мы видим, функционал может быть полезен в различных сценариях, а улучшение действительно заметное как в плане удобства, так и в плане объема хранимых данных.

SQL Server 2016: Live Query Statistics

В SQL Server Management Studio появилась возможность наблюдать за ходом выполнения запроса. По мере выполнения плана запроса отображается взаимодействие между операторами и потоки данных. Этот процесс получил название Live Query Plan и показывает время работы запроса, прогресс на каждом операторе и количество данных. С помощью данного функционала можно более детально изучать работу запросов для их последующей оптимизации. Стоит отметить, что возможность появляется именно в новой SQL Server Management Studio, а работать будет как с SQL Server 2014, так и с SQL Server 2016.

Для того, чтобы увидеть Live Query Plan необходимо на панели инструментов нажать кнопку Include Live Query Statistics.

Также вы можете нажать правой кнопкой мыши на запросе и выбрать необходимый пункт из выпавшего меню.

После этого запустите запрос и сможете увидеть, как он будет выполняться вживую. Выглядеть это будет примерно следующим образом.

Зеленым выделен оператор, который уже завершился, стрелки, обозначающие потоки данных, прямые линии. Синим выделен оператор, который еще в процессе выполнения, потоки данных обозначены пунктиром, процент выполнения растет и количество строк тоже. Для каждого оператора отображается соответственно его время выполнения, оценочный процент выполнения, количество прошедших через него строк и ожидаемое количество строк. Если вдруг оптимизатор оценит количество строк неправильно, то у вас будет отображаться 99% выполнения, а количество строк будет постоянно расти. В этом случае предсказать время выполнения становится сложнее. Выглядеть это будет примерно так.

Также существует возможность посмотреть на Live Query Plan прямо из Activity Monitor путем нажатия правой клавишей мыши на интересующий нас запрос и выбора пункта Show Live Execution Plan.

Но для того, чтобы это сработало необходимо, чтобы в сессии, которая выполняет запрос был включен сбор статистики. Это можно сделать с помощью команд SET STATISTICS XML ON или SET STATISTICS PROFILE ON в нужной сессии, а также включив сбор расширенного события query_post_execution_showplan для нужных сессий. Будьте аккуратны, не активируете это для всех сессий на сервере, т.к. сбор статистики замедляет выполнение запросов.

Функциональность требует наличия прав SHOWPLAN и VIEW SERVER STATE, но эти права обычно всегда выдают тем, кто занимается диагностикой и оптимизацией запросов. Существуют также некоторые ограничения. Live Query Plan недоступен для Natively Compiled хранимых процедур.

SQL Server 2016: увеличен максимальный размер ключа в некластерном индексе

В SQL Server 2016 и в Azure SQL Database увеличен максимальный размер ключа в некластерном индексе с 900 до 1700 байт. Максимальный размер ключа для кластерного индекса по-прежнему остается 900 байт.

Ниже приведен пример, когда создается таблица со строковым полем фиксированной длины 1700 байт и индексом на этом поле.

if object_id('dbo.test_table', 'U') is not null
	drop table dbo.test_table;
go
 
create table dbo.test_table (
	c1 varchar(1700) not null
);
go
 
create index ix_test_c1 on dbo.test_table (c1)
go
 
insert into dbo.test_table values (replicate('A', 1700))
go

Скрипт успешно отрабатывает на SQL Server 2016. На ранних версиях SQL Server команда CREATE INDEX выдает предупреждение.

Warning! The maximum key length is 900 bytes. The index ‘ix_test_c1’ has maximum length of 1700 bytes. For some combination of large values, the insert/update operation will fail.

А при попытке вставить какие данные в таблицу – ошибку.

Msg 1946, Level 16, State 3, Line 13
Operation failed. The index entry of length 1700 bytes for the index ‘ix_test_c1’ exceeds the maximum length of 900 bytes.

При создании некластерных колоночных индексов вы можете указать столбцы, которые будут храниться в колоночном формате. Эти колонки не будут являться ключом индекса, и здесь нет жесткого ограничения на их размер.

Для некластерных индексов в in-memory таблицах ограничение на размер ключа составляет 2500 байт, для hash индексов ограничений нет.

SQL Server 2016: DROP IF EXISTS

Я более чем уверен, что многие из вас писали следующий или очень похожий на него код:

if object_id('dbo.test_table', 'U') is not null
    drop table dbo.test_table;
go
 
create table dbo.test_table (
    id int not null,
    name varchar(100) null
);
go

Объекты из базы иногда необходимо удалять, например, в скриптах развертывания новой версии базы данных, либо при тестировании, когда скрипт, создающий новые объекты запускается подряд несколько раз. А если просто попытаться удалить несуществующий объект, то возникнет ошибка и выполнение скрипта прервется. И если вышеуказанный код проверки и удаления таблицы еще выглядит более-менее читабельно, но проверка на существование триггеров и пользователей представляет из себя уже довольно длинное выражение, например:

if exists (select top (1) 1 from sys.triggers as tr where tr.name = 'tr_test')
    drop trigger tr_test;
go
 
create trigger tr_test
on dbo.test_table
after insert
as
begin
    print 'test';
end;
go

В SQL Server 2016 наконец-то появилась возможность в T-SQL удалять объект с проверкой на его существование. Выглядит эта команда вот так: DROP <тип  объекта> IF EXISTS <название объекта>. Давайте посмотрим, как вышеуказанные команды будут выглядеть в SQL Server 2016.

drop table if exists dbo.test_table;
go
 
drop trigger if exists tr_test;
go

Как видим, теперь это делается гораздо проще и выглядит более понятно. Сейчас новый синтаксис распространяется на следующие типы объектов:

VIEW FUNCTION SEQUENCE INDEX
PROCEDURE TRIGGER DATABASE SECURITY POLICY
TABLE VIEW SCHEMA SYNONYM
ASSEMBLY RULE USER
ROLE TYPE DEFAULT

 

Казалось бы, небольшое изменение, но оно очень упрощает работу, т.к. все, что облегчает рутинные операции и минимизирует количество написанного для них кода, в конечном счете увеличивает нашу производительность. Ну и напоследок хочу добавить от себя ложку дегтя. Очень хотелось бы получить команду CREATE OR REPLACE, как в Oracle, т.к. тогда вообще отпадала бы необходимость писать команду удаления. Возможно, в будущих версиях нас все-таки порадуют.