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 хранимых процедур.

Подводные камни INSERT EXEC

Выражение INSERT EXEC достаточно удобно во многих отношениях, т.к. позволяет вставить в таблицу данные из динамического запроса или хранимой процедуры. Это довольно привлекательный способ, т.к. позволяет один раз написать логику выборки и предоставить интерфейс в виде хранимой процедуры, который уже будет вызываться в остальных местах: других хранимых процедур, для получения данных с удаленного сервера и т.п. Логика находится только в одном месте и ее достаточно удобно изменить при необходимости. Однако, в этом решении есть свои подводные камни, и я предлагаю посмотреть на них на примере.

Для начала мы создадим пару таблиц: источник и приемник, добавим в источник около 100 тысяч записей, а также процедуру, которая возвращает данные из источника.

use [tempdb];
go

if object_id('dbo.test_table_src', 'U') is not null
	drop table dbo.test_table_src;
go

if object_id('dbo.test_table_tgt', 'U') is not null
	drop table dbo.test_table_tgt;
go

if object_id('dbo.get_test_table_src', 'P') is not null
	drop procedure dbo.get_test_table_src;
go

create table dbo.test_table_src (
	[id] int not null identity(1, 1),
	[c1] int not null,
	[c2] varchar(255) not null,
	[c3] datetime not null,

	constraint [pk_test_table_src] primary key clustered ([id])
);
go

create procedure [dbo].[get_test_table_src]
as
begin
	set nocount on;

	select
		[id],
		[c1],
		[c2],
		[c3]
	from [dbo].[test_table_src];
end;
go

create table dbo.test_table_tgt (
	[id] int not null,
	[c1] int not null,
	[c2] varchar(255) not null,
	[c3] datetime not null,

	constraint [pk_test_table_tgt] primary key clustered ([id])
);
go

insert into [dbo].[test_table_src] (
	[c1],
	[c2],
	[c3]
)
select top (100000)
	cast(rand(checksum(newid())) * 1000000 as int),
	cast(newid() as varchar(100)) + '_' + cast(newid() as varchar(100)),
	dateadd(second, cast(rand(checksum(newid())) * 1000000 as int), '19000101')
from sys.all_columns as t1
cross join sys.all_columns as t2
go

Для начала давайте включим статистику времени выполнения и IO операций и просто сделаем вставку в таблицу приемник из источника.

set statistics io on;
set statistics time on;
go
insert into [dbo].[test_table_tgt] (
	[id],
	[c1],
	[c2],
	[c3]
)
select
	[id],
	[c1],
	[c2],
	[c3]
from [dbo].[test_table_src];
go

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

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table ‘test_table_tgt’. Scan count 0, logical reads 257193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘test_table_src’. Scan count 1, logical reads 1272, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 188 ms,  elapsed time = 226 ms.

(100000 row(s) affected)

А теперь посмотрим, за сколько отработает запрос INSERT EXEC в таблицу приемник с помощью хранимой процедуры. Перед запуском я предварительно очищаю таблицу от результатов предыдущего теста.

truncate table [dbo].[test_table_tgt];
alter table [dbo].[test_table_tgt] rebuild;
go
insert into [dbo].[test_table_tgt]
exec [dbo].[get_test_table_src];
go

И статистика для этого запроса.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Table ‘test_table_src’. Scan count 1, logical reads 1271, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 671 ms,  elapsed time = 669 ms.

Table ‘test_table_tgt’. Scan count 0, logical reads 257193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 1, logical reads 306169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 921 ms,  elapsed time = 927 ms.

(100000 row(s) affected)

Как мы видим в статистике, появляется Worktable (временная таблица в tempdb), с которой происходит очень большое количество операций. Это получается из-за того, что перед тем, как вставить данные в таблицу приемник, они сначала временно материализуются в tempdb, а уже только потом вставляются. В итоге на моей машине, да и на нескольких других, где я проводил подобный тест, INSERT EXEC работает примерно в 4 раза медленнее, чем обычный INSERT. Да, мы лишаемся удобного интерфейса для получения данных, но если для нас важнее скорость, то лучше всеми способами избегать использовать INSERT EXEC.

Про скорость поиска по строкам и таблицу с настройками

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

use [tempdb];
go


if object_id('dbo.settings', 'U') is not null
	drop table dbo.settings;
go

create table dbo.settings (
	[name] varchar(128) not null,
	[value] sql_variant null,

	constraint [pk_settings] primary key clustered ([name])
);
go

Настройки могут быть самые разные: какие-то процессы могут постоянно обновлять свой статус, хранить дату последней обработки данных; также это могут быть какие-то параметры для внешних сервисов или ETL процессов. В общем, способов применения очень много, и такой подход встречается очень часто. Но я, конкретно, хотел бы остановиться на одной довольно распространенной ошибке, которая встречается в этом случае, да и в некоторых других. Очень часто названия параметров содержат довольно большой одинаковый префикс, например: Process1_config_param1, Process1_last_active_time, Process2_config_param1, Process2_last_active_time и т.п. Я надеюсь, вы уловили смысл. У нас в базе данных могут быть десятки, сотни или даже тысячи строк, у которых 5-30 первых символов совпадает. И это может повлиять на производительность, т.к. при поиске по названию придется проверять все эти строки. Давайте устроим небольшой тест на производительность и посмотрим, как себя ведет SQL Server.

В первом тесте я вставлю 1000 значений в таблицу, у которых первые 10 символов будут одинаковые, а последние 4 уникальные, и запущу подряд 3 миллиона выборок по одному из значений.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select replicate('A', 10) + replicate('0', 4 - ceiling(log(rn + 1, 10))) + cast(rn as varchar(10)), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;

declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = 'AAAAAAAAAA0100'

	set @i += 1

end

В следующем тесте я сделаю первые 4 символа уникальные, а остальные совпадающие. Т.е. я убрал все повторяющиеся куски в конец строки, а уникально идентифицирующие в начало.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000') + replicate('A', 10), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100AAAAAAAAAA'

	set @i += 1

end

Ну и в финальном тесте я вообще убрал повторяющиеся значения, а оставил только короткие уникальные наименования.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000'), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100'

	set @i += 1

end

Вы можете запустит все 3 теста у себя и сравнить результаты. Я же хочу поделиться своими. Тесты запускались несколько раз, результаты были достаточно стабильны. Точное время выполнения приводить не имеет смысла, поделюсь лишь сравнительными итогами. Если взять за основу первое решение, то второй вариант относительно него выполняется на 10% быстрее, что очень существенно. Если взять третий тест, то он будет приблизительно на 15% быстрее чем первый и чуть больше, чем на 5% быстрее, чем второй.

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

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 индексов ограничений нет.

О том, что всегда нужно использовать псевдонимы

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

use tempdb;
go

if object_id('tempdb..#t1', N'U') is not null
	drop table #t1;

create table #t1 (
	id int not null primary key,
	some_value char(10) not null
);

insert into #t1 (id, some_value)
values
	(1, 'A'), (2, 'B');

-- Пример запроса без использования псевдонимов.
select id, some_value
from #t1;

-- Пример запроса с использованием псевдонимов.
select t1.id, t1.some_value
from #t1 as t1;

А во-вторых, если вы используете более двух таблиц в запросе, и в них имеются столбцы с одинаковым именем, то вам необходимо указывать название таблицы или ее псевдоним, чтобы уточнить, к какому именно столбцу вы обращаетесь.

use tempdb;
go

if object_id('tempdb..#t1', N'U') is not null
	drop table #t1;

if object_id('tempdb..#t2', N'U') is not null
	drop table #t2;

create table #t1 (
	id int not null primary key,
	some_value char(10) not null
);

create table #t2 (
	id int not null primary key,
	some_value char(10) not null
);

insert into #t1 (id, some_value)
values
	(1, 'A'), (2, 'B');

insert into #t2 (id, some_value)
values
	(1, 'C'), (2, 'D');

-- Пример корректно написанного запроса.
select t1.id, t2.some_value
from #t1 as t1
inner join #t2 as t2 on
	t2.id = t1.id;

-- Пример запроса, который вызовет ошибку.
select id, some_value
from #t1 as t1
inner join #t2 as t2 on
	t2.id = t1.id;

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

Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ‘t2’.

Однако это еще не все. Иногда игнорирование использования псевдонимов в запросе может приводить к трудно отлавливаемым ошибкам и некорректным результатам.

use tempdb;
go

if object_id('tempdb..#t1', N'U') is not null
	drop table #t1;

if object_id('tempdb..#t2', N'U') is not null
	drop table #t2;

create table #t1 (
	id int not null,
	some_id int not null
);

create table #t2 (
	no_id int not null
);

insert into #t1 (id, some_id)
values
	(1, 1),
	(2, 3);

insert into #t2 (no_id) values (5);

-- В таблице #t2 отсутствует поле id, но запрос при этом выполняется без ошибок.
select *
from #t1 as t1
where
	some_id in (select id from #t2);

В данном случае мы предполагали, что поле id присутствует во временной таблице #t2, хотя его там нет. Запрос при этом отработал без ошибок и вернул данные, т.к. написан он корректно: вернул те записи, у которых поля id и some_id совпадают, если в таблице #t2 имеется хоть одна запись. Как мы видим, интерпретация запроса получилась совсем другая, которую мы имели ввиду, когда его составляли. Поэтому во избежание подобных случайных ошибок в своих запросах рекомендуется всегда использовать псевдонимы и точно указывать, к каким таблицам и столбцам вы обращаетесь.

Как посмотреть процент отката транзакции в SQL Server

Предположим такую ситуацию: у вас происходит откат (rollback) какого-либо запроса или транзакции, а вы хотите посмотреть, когда он закончится. Одним из самых простых способов для этого является, как ни странно, команда kill с опцией statusonly. Выглядеть это будет примерно следующим образом.

kill 64 with statusonly

На выходе вы получите примерно следующий результат.

SPID 64: transaction rollback in progress. Estimated rollback completion: 60%. Estimated time remaining: 13508 seconds.

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

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, т.к. тогда вообще отпадала бы необходимость писать команду удаления. Возможно, в будущих версиях нас все-таки порадуют.

SQL Server 2016: Dynamic Data Masking

В SQL Server 2016 появляется новая возможность Dynamic Data Masking, которая позволяет ограничить видимость важных данных для непривилегированных пользователей путем ограничения области видимости. Вы сами сможете указывать, какую часть из этих данных можно отображать. При этом данные в самой базе остаются неизменными, а также не потребуются доработки со стороны существующих приложений.

Давайте рассмотрим это на примере. Допустим у нас в базе данных хранятся номера кредитных карт пользователей, и у нас есть оператор, которому необходимо видеть только 4 последних цифры этого номера (вы должны были довольно часто сталкиваться с этим, при общении с представителями банков). С помощью Dynamic Data Masking мы можем на уровне базы данных задать маску, которая скроет от оператора все цифры кредитных карт, кроме последних четырех.

Существует 4 вида функций, которые можно применять для сокрытия данных.

default – Для строковых полей отображает XXXX, для числовых типов – 0, для дат – 1 января 1900г., а для бинарных – 0x30 (это не что иное, как бинарное представление ASCII кода от символа 0). Null значения не скрываются. Давайте посмотрим на небольшой пример, как это будет работать. В примере я создаю таблицу с колонками различных типов данных, вставляю туда несколько разных значений и проверяю, как отображение работает из-под владельца базы данных и пользователя с правом только на выбор данных.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	c1_varchar varchar(100) masked with (function = 'default()') null,
	c2_varchar_max varchar(max) masked with (function = 'default()') null,
	c3_nvarchar varchar(100) masked with (function = 'default()') null,
	c4_nvarchar_max varchar(max) masked with (function = 'default()') null,
	c5_int int masked with (function = 'default()') null,
	c6_bit bit masked with (function = 'default()') null,
	c7_uniqueidentifier uniqueidentifier masked with (function = 'default()') null,
	c8_datetime datetime masked with (function = 'default()') null,
	c9_varbinary varbinary(100) masked with (function = 'default()') null,
	c10_varbinary_max varbinary(max) masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	c1_varchar,
	c2_varchar_max,
	c3_nvarchar,
	c4_nvarchar_max,
	c5_int,
	c6_bit,
	c7_uniqueidentifier,
	c8_datetime,
	c9_varbinary,
	c10_varbinary_max
)
values
	('q', 'q', 'qwerty_asdfg', 'qwerty_asdfg', 1, 1, newid(), getutcdate(), 0x01, 0x000102030405),
	('qw', 'qw', 'qwer', 'qwer', 2, 0, newid(), getutcdate(), 0x02, 0x00),
	('qwe', 'qwe', 'qwe', 'qwe', 3, 1, newid(), getutcdate(), 0x03, 0x02),
	('qwer', 'qwer', 'qw', 'qw', 4, 0, newid(), getutcdate(), 0x04, 0x01),
	('qwerty_asdfg', 'qwerty_asdfg', 'q', 'q', 5, 1, '00000000-0000-0000-0000-000000000000', '19000101', 0x30, 0x30),
	(null, null, null, null, null, null, null, null, null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

email – данная функция, как легко догадаться, предназначена для сокрытия адресов электронной почты и работает по следующему алгоритму: отображает первый символ адреса, а после него показывает XXX@XXXX.com, независимо от того, в каком домене у вас адрес, на конце всегда .com. Соответствующий пример для функции email будет выглядеть так:

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	email varchar(200) masked with (function = 'email()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	email
)
values
	('qwe@qwe.com'),
	('zxc@asdzxc.ru'),
	(null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

partial – позволяет более гибко управлять отображением строковых значений. Вы указываете эту функцию в формате partial(N1, “XXXXXXX”, N2), где N1 – количество символов с начала строки, которые можно показать, N2 – с конца, а между ними указываете произвольную маску, которая отобразится вместо остального текста. Если вдруг длина строки не будет превышать указанного количества открытых символов, то вместо нее просто отбразится маска. Давайте посмотрим, как, например, с помощью этой функции можно скрыть номера телефонов, кредитных карт и просто произвольной строки разной длины.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	phone_number varchar(20) masked with (function = 'partial(3, "-XXX-XX-", 2)') null,
	credit_card char(19) masked with (function = 'partial(0, "XXXX-XXXX-XXXX-", 4)') null,
	custom_string varchar(100) masked with (function = 'partial(5, "XXXXX", 5)') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	phone_number,
	credit_card,
	custom_string
)
values
	('99112345671', '1111-1111-1111-1111', 'qwerty'),
	('99212345672', '1111-1111-1111-2222', 'qwe'),
	('99312345673', '1111-1111-1111-3333', '123456789A'),
	('99412345674', '1111-1111-1111-3333', '123456789AB'),
	('99512345675', '1111-1111-1111-4444', '123456789ABCDEF'),
	(null, null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

random – четвертая функция предназначена для сокрытия числовых типов данных, но в отличии от функции default она позволяет не просто отображать нули, а некое случайно число в заданном диапазоне. Например, для колонки, где у нас указан возраст клиентов, можно генерировать некое случайно число от 18 до 100. Используется в формате random(<начало диапазона>, <конец диапазона>). И давайте посмотрим на соответствующий пример для этой функции:

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	age tinyint masked with (function = 'random(18, 100)') null,
	month tinyint masked with (function = 'random(1, 12)') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	age,
	month
)
values
	(18, 1),
	(19, 2),
	(20, 3),
	(30, 4),
	(55, 5),
	(null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

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

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

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	tmp varchar(100) masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	tmp
)
values
	('qwe');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * into #tmp from dbo.test;
select * from #tmp;
revert;
go

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

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	tmp1 varchar(100) null,
	tmp2 varchar(100) masked with (function = 'default()') null,
	tmp3 varchar(100) masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	tmp1,
	tmp2,
	tmp3
)
values
	('qwe1@qwe.com', 'qwe2@qwe.com', 'qwe3@qwe.com');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

alter table [dbo].[test]
alter column tmp1 add masked with (function = 'default()');
go

alter table [dbo].[test]
alter column tmp2 drop masked;
go

alter table [dbo].[test]
alter column tmp3 varchar(100) masked with (function = 'email()');
go

execute as user = 'test_user';
select * from dbo.test;
revert;

grant unmask to test_user;
execute as user = 'test_user';
select * from dbo.test;
revert;

revoke unmask to test_user;
execute as user = 'test_user';
select * from dbo.test;
revert;
go

Есть также некоторые ограничения, которые действуют на замаскированные колонки: они не могут быть зашифрованы с помощью Always Encrypted, не поддерживается FILESTREAM, COLUMN_SET, вычисляемые столбцы, а также они не могут быть ключом для FULLTEXT индекса. Как видим, ограничения довольно небольшие и вполне ожидаемые, поэтому я не считаю их критичными.

Хотя Dynamic Data Masking предназначен для скрытия данных от того, кто их не должен видеть, он не спасет вас, если пользователь будет напрямую подключаться к баз данных и запускать запросы, которые будут вычислять скрытые данные. Эта возможность является лишь дополнением к остальным функциям, обеспечивающим безопасность данных (распределение прав доступа, шифрование, аудит и т.п.). Вы должны четко понимать модель угроз для ваших данных, чтобы правильно выбрать необходимые технологии для их защиты. В конце я хочу привести пример, который может вам позволить вычислять, какие же символы стоят в строке на определенных позициях, даже если строка от вас скрыта.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	tmp text masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	tmp
)
values
	('qwe');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select *, datalength(tmp), substring(tmp, 1, 1) from dbo.test;
select * from dbo.test where substring(tmp, 1, 1) = 'q';
select * from dbo.test where substring(tmp, 1, 1) = 'w';
revert;
go

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

На этом я бы хотел закончить рассказ о Dynamic Data Masking в SQL Server 2016. Получилось довольно объемно, но, на мой взгляд, удалось рассказать все самое важное об этом функционале с примерами.