Category Archives: T-SQL

Различия между операторами SET и SELECT при присваивании значений переменным

Leave a reply

В SQL Server в языке T-SQL имеется два оператора SET и SELECT, и они оба могут использоваться для присваивания значений переменным. В некоторых ситуациях использование того или иного оператора может привести к неожиданным и непредсказуемым результатам. В этой статье я бы хотел подробно рассмотреть различия между ними и рассказать о различных ловушках, в которые вы можете попасть.

В первую очередь, оба оператора могут равнозначно использоваться для присваивания фиксированных значений переменных. Однако, оператор SET является стандартом для языка SQL, в то время как SELECT является особенностью только T-SQL диалекта в SQL Server.

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = 1;
set @dt_example = getdate();
set @str_example = 'qwe';

select @int_example, @dt_example, @str_example;

select @int_example = 1;
select @dt_example = getdate();
select @str_example = 'qwe';

select @int_example, @dt_example, @str_example;
go

Однако, если мы хотим за одну операцию инициализировать сразу несколько переменных, то необходимо использовать оператор SELECT.

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

select
    @int_example = 1,
    @dt_example = getdate(),
    @str_example = 'qwe';

select @int_example, @dt_example, @str_example;
go

Если же мы попытаемся использовать в данной ситуации SET, то получим ошибку, т.к. он просто не поддерживает такую операцию.

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set
    @int_example = 1,
    @dt_example = getdate(),
    @str_example = 'qwe';

select @int_example, @dt_example, @str_example;
go

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

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

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = (select int_example from #tmp where id = 1);
select @int_example;

select @int_example = int_example from #tmp where id = 2;
select @int_example;
go

Единственное, необходимо учитывать, что, если результат запроса к таблице вернет больше одного значения, то в случае с операцией SET мы получим ошибку.

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = (select int_example from #tmp where id = 3);
select @int_example;
go

Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

В случае же использования оператора SELECT ошибки не возникнет, но в общем случае мы не можем точно предугадать, какое из удовлетворяющих условию выборки значений будет присвоено переменной. Рассмотрим первый случай, в котором запрос вернет 32.

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

select @int_example = int_example from #tmp where id = 3;
select @int_example;
go

В следующем случае запрос возвращает 31.

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 32, '19030202', 'qwe32'),
    (3, 31, '19030101', 'qwe31');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

select @int_example = int_example from #tmp where id = 3;
select @int_example;
go

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

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = 5;
set @int_example = (select int_example from #tmp where id = 4);
select @int_example;

set @int_example = 5;
select @int_example = int_example from #tmp where id = 4;
select @int_example;
go

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

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = 5;
set @int_example = (select int_example from #tmp where id = 4);
select @@rowcount;
select @int_example;

set @int_example = 5;
select @int_example = int_example from #tmp where id = 4;
select @@rowcount;
select @int_example;
go

Подведем итоги, вы можете использовать оба оператора, но должны четко понимать, в какой ситуации и что от них ожидать.
Используйте оператор SET, если:

  • Если вы присваиваете фиксированные значения переменным без использования запросов и хотите следовать стандартам.
  • Ожидаете, что переменной будет присвоено значение NULL, если запрос не вернул никаких результатов.
  • Запрос может вернуть несколько значений, и вы хотите отслеживать такие ситуации.

И используйте оператор SELECT, если:

  • Хотите за одну инструкцию присвоить значения сразу нескольким переменным.
  • В случае выборки значения из таблицы готовы контролировать, действительно ли произошла инициализация, например, с помощью @@rowcount.

Преобразование в Base64 и обратно

4 Replies

Хочу поделиться способом преобразования строки в Base64 и обратно. Сразу оговорюсь, что способ не самый быстрый, но главное его преимущество в том, что он работает прямо «из коробки» без применения специальных CLR функций. Нам потребуется тестовая база данных и таблица для примера:

use [master];
go

if db_id('Base64_test') is not null
begin
	alter database [Base64_test] set single_user with rollback immediate;
	drop database [Base64_test];
end
go

create database [Base64_test];
go

use [Base64_test];

create table [dbo].[Base64_test] (
	[plain_text] nvarchar(max),
	[base64_text] nvarchar(max)
);
go

insert into [dbo].[Base64_test] ([plain_text], [base64_text])
values (N'My Test String', N'TQB5ACAAVABlAHMAdAAgAFMAdAByAGkAbgBnAA==');
go

Пример конвертации строки в Base64:

-- Конвертируем значение переменной
declare @value varbinary(max);
set @value = cast(N'My Test String' as varbinary(max));
select cast(N'' as xml).value('xs:base64Binary(sql:variable("@value"))', 'varchar(max)') as [encoded_value];
go

-- Конвертируем значение столбца в таблице
select cast(N'' as xml).value('xs:base64Binary(sql:column("plain_text"))', 'varchar(max)') as [encoded_value]
from (
	select cast([plain_text] as varbinary(max)) as [plain_text]
	from [dbo].[Base64_test]
) as tmp;
go

Пример обратного преобразования:

-- Конвертируем значение
select cast(cast(N'' as xml).value('xs:base64Binary("TQB5ACAAVABlAHMAdAAgAFMAdAByAGkAbgBnAA==")', 'varbinary(max)')  as nvarchar(max)) as [decoded_value];
go

-- Конвертируем значение переменной
declare @value varchar(max)
set @value = 'TQB5ACAAVABlAHMAdAAgAFMAdAByAGkAbgBnAA=='
select cast(cast(N'' as xml).value('xs:base64Binary(sql:variable("@value"))', 'varbinary(max)')  as nvarchar(max)) as [decoded_value];
go

-- Конвертируем значение столбца в таблице
select cast(cast(N'' as xml).value('xs:base64Binary(sql:column("base64_text"))', 'varbinary(max)') as nvarchar(max)) as [decoded_value]
from [dbo].[Base64_test];
go

Статистические функции STDEV, STDEVP, VAR, VARP

3 Replies

Среди функций агрегации в SQL Server присутствуют STDEV, STDEVP, VAR и VARP, которые могут вызвать вопросы, что это такое, как и когда их применять. Для начала я приведу формулы, по которым идем расчет указанных функций. Функции взяты из математической статистики.

STDEV возвращает статистическое среднеквадратическое отклонение всех значений в указанном выражении.

 — дисперсия;  — i-й элемент выборки;  — объём выборки;  — среднее арифметическое выборки:

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

VAR возвращает статистическую дисперсию всех значений в указанном выражении. Значение равно (квадрат значения, возвращаемого функцией STDEV).

VARP Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении (квадрат значения, возвращаемого функцией STDEVP).

Т.к. функции VAR и VARP фактически дублируют STDEV и STDEVP, то сосредоточимся только на последних. Перепишем формулы их вычисления в чуть более понятный на мой взгляд вид.

Stdevp = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + … + (xn-xmean)^2)/n )

Stdev = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + … + (xn-xmean)^2)/(n-1) )

По сути эти функции показывают насколько в среднем значения отличаются от арифметического среднего. Теперь давайте рассмотрим небольшой пример их применения. Возьмем простую таблицу со столбцом типа int, 3мя записями и рассчитаем для них значения функций.

USE [tempdb];
GO

IF OBJECT_ID('tempdb.dbo.test', 'U') IS NOT NULL
    DROP TABLE [dbo].[test];
GO

CREATE TABLE [dbo].[test] (
    [d] [int] NOT NULL
);
GO

INSERT INTO [dbo].[test] ([d]) VALUES (1), (2), (3);
GO

SELECT
    AVG([d]) AS [avg]
    ,STDEV([d]) AS [stdev]
    ,STDEVP([d]) AS [stdevp]
FROM [dbo].[test];
GO


Значение STDEV на небольших выборках, как мы видим может быть более точным, нежели STDEVP (по сути исключается из расчетов одно значение, которое либо само является средним, либо близко к среднему). Но на больших выборках они могут быть очень близки.

Т.е. эти функции удобно применять, когда требуется вычислить все значения в таблице, которые сильно отклоняются от среднего. Есть даже такое правило трех сигм (), которое утверждает, что практически все значения выборки будут лежать в диапазоне 3х сигм от среднего.

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

USE [tempdb];
GO

IF OBJECT_ID(‘tempdb.dbo.test’, ‘U’) IS NOT NULL
    DROP TABLE [dbo].[test];
GO

CREATE TABLE [dbo].[test] (
    [d] [int] NOT NULL
);
GO

INSERT INTO [dbo].[test] ([d]) VALUES (1), (2), (1), (1), (0), (1), (1), (2), (1), (1), (15);
GO

SELECT
    [d]
    ,CASE WHEN ABS([d] - AVG([d]) OVER() ) > 3 * STDEV([d]) OVER() THEN 1 ELSE 0 END IsLargeThan3Sigma
FROM [dbo].[test];
GO

Влияет ли PRIMARY KEY в табличной переменной на ожидаемое количество строк при выборке

2 Replies

Как мы знаем, статистика не создается на табличных переменных и оптимизатор SQL Server считает, что ожидаемое количество строк при выборке из табличной переменной будет равно единице. На прошедшей конференции SQL Rally 2012 после моего доклада прозвучал очень интересный вопрос: будет ли влиять PRIMARY KEY в табличной переменной на ожидаемое количество строк при выборке. Я решил проверить это на практике, а так как результат, на мой взгляд, получился очень интересным, я решил им поделиться.

Для нашего эксперимента я создам 2 временных таблицы, одну с PRIMARY KEY, а вторую без, и 2 аналогичных табличных переменных. И вставлю в них значения от 1 до 100. И давайте попробуем сделать несколько тестовых выборок из этих таблиц с разными предикатами, а потом проанализируем результаты. Перед запуском скрипта необходимо включить  отображение реального плана выполнения запроса (можно сделать несколькими способами: сочетанием клавиш Ctrl+M, либо через меню Query -> Include Actual Execution Plan, либо нажать соответствующую кнопку на панели инструментов).

-- Создаем временную таблицу с числами от 1 до 1000
IF OBJECT_ID('tempdb.dbo.#nums', 'U') IS NOT NULL
	DROP TABLE #nums;

CREATE TABLE #nums (
	[n] int PRIMARY KEY
);

DECLARE @max AS INT, @rc AS INT;
SET @max = 800;
SET @rc = 1;

INSERT INTO #nums ([n]) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
	INSERT INTO #nums ([n]) SELECT [n] + @rc FROM #nums;
	SET @rc = @rc * 2;
END

INSERT INTO #nums ([n])
SELECT [n] + @rc FROM #nums WHERE [n] + @rc <= @max;

/*
Создаем 2 временные таблицы: одну с PRIMARY KEY, вторую без.
И 2 аналогичные табличные переменные. Вставляем в них числа от 1 до 1000.
*/
IF OBJECT_ID('tempdb.dbo.#temp_table', 'U') IS NOT NULL
	DROP TABLE #temp_table;

IF OBJECT_ID('tempdb.dbo.#temp_table_pk', 'U') IS NOT NULL
	DROP TABLE #temp_table_pk;

CREATE TABLE #temp_table (
	[i] int
);

CREATE TABLE #temp_table_pk (
	[i] int PRIMARY KEY
);

DECLARE @table_var TABLE (
	[i] int
);

DECLARE @table_var_pk TABLE (
	[i] int PRIMARY KEY
);

INSERT INTO #temp_table ([i])
SELECT [n] FROM #nums;

INSERT INTO #temp_table_pk ([i])
SELECT [n] FROM #nums;

INSERT INTO @table_var ([i])
SELECT [n] FROM #nums;

INSERT INTO @table_var_pk ([i])
SELECT [n] FROM #nums;

-- Делаем несколько тестовых запросов
SELECT [i] FROM #temp_table WHERE [i] < 145;
SELECT [i] FROM #temp_table_pk WHERE [i] < 145;

SELECT [i] FROM #temp_table WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM #temp_table_pk WHERE [i] > 378 AND [i] < 634;

SELECT [i] FROM @table_var WHERE [i] < 145;
SELECT [i] FROM @table_var WHERE [i] < 145 OPTION (RECOMPILE);

SELECT [i] FROM @table_var_pk WHERE [i] < 145;
SELECT [i] FROM @table_var_pk WHERE [i] < 145 OPTION (RECOMPILE);

SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE);

SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE);

А теперь давайте подробно проанализируем планы наших запросов.

1. Выборка и временной таблицы с фильтром по верхней границе.

Результаты запроса

Результаты запроса

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

2. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы.

Результаты запроса

Результаты запроса

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

3. Выборка из табличной переменной с фильтром по верхней границе.

Привожу сразу планы запросов для обычной табличной переменной и табличной переменной с PRIMARY KEY.

Результаты запроса

Результаты запроса

Результаты запроса

Результаты запроса

Как мы видим и в том и в другом случае количество ожидаемых строк равно единице. Таким образом, PRIMARY KEY никак не влияет на ожидаемое количество строк при выборке из табличной переменной.

4. Выборка из табличной переменной с опцией RECOMPILE. Привожу только план выборки из обычной табличной переменной, т.к. для табличной переменной с PRIMARY KEY будет аналогично.

Результаты запроса

Результаты запроса

Результаты оказались несколько неожиданными: оптимизатор ожидает на выходе 300 строк вместо одной. 300 строк – это 30%, обычная константа, которая зашита в оптимизатор.

5. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы. Сначала приведу обычный план запроса, потом план запроса с опцией RECOMPILE.

Результаты запроса

Результаты запроса

Результаты запроса

Результаты запроса

В первом плане запросов ожидаемое количество строк равно единице. А вот для запроса с опцией RECOMPILE количество ожидаемых строк равно 90 или 9% от общего числа. Я поэкспериментировал с различным количеством строк в таблице и разными границами предиката, но результата один – 9%. Для запросов к табличным переменным с PRIMARY KEY результаты будут такие же, поэтому планы запросов не привожу.

Вывод.

Пришла пора подвести итог небольшого практического исследования и ответить на главный вопрос статьи: наличие PRIMARY KEY на табличной переменной никак не влияет на ожидаемое количество строк при выборке.

Но в процессе исследования случайно столкнулся с тем, что если использовать опцию RECOMPILE, то количество ожидаемых строк будет 30% от общего числа строк, если использовать в предикате ограничение только по одной границе интервала. А если использовать ограничение с двух сторон, то количество ожидаемых строк будет 9% от общего числа строк в табличной переменной.