Tag Archives: T-SQL

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

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

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

В 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.

Материалы с конференции DevCon 2013

2 недели назад в подмосковных Яхонтах прошла очередная конференция DevCon 2013. Выкладываю материалы моих докладов.

Как читать план запроса в SQL Server и на что обращать внимание

В докладе идет речь о том, что такое план запроса; о различных операторах, с которыми можно встретиться, с чего начать анализ плана, на что обращать внимание и как можно выявить “узкое место” в запросе.

Презентация: http://olontsev.ru/download/DevCon2013_Query_Plans.pdf

Скрипты с демонстрациями: http://olontsev.ru/download/Query_Plans_Demo_Scripts.zip

Особенности использования хранимых процедур и функций в SQL Server

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

Презентация: http://olontsev.ru/download/DevCon_2013_UDFs_and_SPs.pdf

Скрипты с демонстрациями: http://olontsev.ru/download/UDFs_and_SPs_Demo_Scripts.zip

Записи докладов можно посмотреть по указанной ссылке (в скором времени должны выложить на http://www.techdays.ru/): http://www.msdevcon.ru/online

Ну и напоследок ответ на самый главный вопрос конференции: в чем секрет белки? J

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

Хочу поделиться способом преобразования строки в 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

Среди функций агрегации в 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() ) &gt; 3 * STDEV([d]) OVER() THEN 1 ELSE 0 END IsLargeThan3Sigma
FROM [dbo].[test];
GO

Использование CONTEXT_INFO

SQL Server обладает широким набором интересных функций. Одной из них является возможность реализовать передачу определенной информации в пределах одной сессии. Когда это может быть нужно? Например, с одной таблицей в базе данных работает несколько приложений и каждое вводит или изменяет данные с помощью своей хранимой процедуры, а триггер в таблице на изменение данных должен это учитывать и вести себя по-разному для всех приложений. В этом случае как раз можно использовать следующий сценарий: хранимая процедура сохраняет определенное значение в переменной сессии, а триггер считывает это значение и выполняет нужной действие в зависимости от контекста. Также эту технику можно применять при использовании вложенных триггеров (nested triggers).

Переменная сессии CONTEXT_INFO позволяет сохранять данные переменной длины размером до 128 байт. Это немного, но для большинства задач должно хватить. Чтобы установить значение переменной сессии нужно воспользоваться командой SET CONTEXT_INFO, а чтобы получить текущее значение – функцией CONTEXT_INFO(). Ну и сразу приведу небольшой пример.

DECLARE @context_info varbinary(128)
SET @context_info = CAST('MyApplicationID1' AS varbinary(128))
SET CONTEXT_INFO @context_info
GO
 
SELECT CONTEXT_INFO()
SELECT CAST(CONTEXT_INFO() AS varchar(128))
GO

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

Также переменную сессии можно посмотреть в следующих системных представлениях в столбце context_info.

  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.sysprocesses

Также можно использовать технику, когда в CONTEXT_INFO мы будем хранить не одно, а несколько значений, но в разных местах. Например, мы договариваемся, что в позиции с 1 по 20 мы храним идентификатор процесса, а 21 по 28 – дату и время. Тогда процесс формирования нашей строки будет выглядеть примерно следующим образом.

DECLARE @process_id char(20) = 'MyProcessID2'
DECLARE @date_time datetime = GETDATE()
DECLARE @context_info varbinary(128)
 
SET @context_info = CAST(@process_id AS binary(20)) + CAST(@date_time AS binary(8))
 
SET CONTEXT_INFO @context_info
 
SELECT CAST(SUBSTRING(CONTEXT_INFO(), 1, 20) AS char(20)) AS process_id, CAST(SUBSTRING(CONTEXT_INFO(), 21, 8) AS datetime) AS date_time

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

DECLARE @context_info varbinary(128)

SET @context_info = CAST('Value before transaction' AS varbinary(128))
SET CONTEXT_INFO @context_info
SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value before transaction]

BEGIN TRAN

	SET @context_info = CAST('Value inside transaction' AS varbinary(128))
	SET CONTEXT_INFO @context_info
	SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value inside transaction]

ROLLBACK

SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value after transaction]

Финальным результатом вернется значение, которое мы установили внутри транзакции, несмотря на то, что транзакцию мы откатили. Это нужно учитывать или можно использовать.

Как определить, используются ли устаревшие функции

Окружающий нас мир постоянно меняется, меняется и SQL Server. От версии к версии появляются новые возможности, а некоторые старые наоборот – исчезают. И если с новыми возможностями все просто – начинаем их использовать, то с устаревшими дела обстоят несколько иначе: в случае обновления SQL Server на новую версию, у нас могут перестать работать некоторые старые запросы. Устаревающий функционал (deprecated features), существует еще в течение нескольких редакций перед тем, как его полностью исключат. Таким образом, перед обновлением нам необходимо постараться определить, какие возможности в новой версии SQL Server исчезают и используются ли они на нашем сервере. Компания Microsoft публикует достаточно подробное описание изменений. Например, для SQL Server 2008 R2 можно прочитать здесь, а для SQL Server 2012 здесь.

Узнать, используется ли у нас устаревший функционал можно достаточно легко, воспользовавшись системным представлением sys.dm_os_performance_counters, которое выводит значения счетчиков производительности. С помощью такого нехитрого запроса можно вывести список всех устаревших функций и сколько раз они использовались с момента последнего старта сервера.

USE [master];
GO
 
SELECT instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:Deprecated Features%'
AND cntr_value &gt; 0
ORDER BY cntr_value DESC;
GO

Пример результатов:

Как мы видим из результатов запроса, названия говорят сами за себя: в качестве псевдонимов столбцов используются строковые константы, несколько старых системных таблиц, типы данных ntext или image и т.п. Далее вам остается только выявить, какие из этих опций будут удалены в той версии, на которую вы планируете обновиться и постараться избавиться от их использования.

Функция IS_MEMBER

Очень часто требуется написать хранимую процедуру или функцию, которая будет выдавать разные результаты для разных групп пользователей. Например, директор предприятия хочет видеть информацию по всем сотрудникам его фирмы, а начальники отделов только по своим сотрудникам. Реализовать это можно, например, включив пользователей в разные группы AD, а в запросе проверять вхождение и в зависимости от результата выдавать разные данные. В SQL Server существует функция IS_MEMBER способная осуществлять вышеуказанную проверку, причем она работает даже с вложенными группами. Функция принимает единственный параметр – название AD группы и возвращает 1, если текущий пользователь входит в указанную группу; 0 – если не входит и NULL, если такой группы в AD не найдено. Естественно, пользователи должны использовать Windows аутентификацию, чтобы воспользоваться этим функционалом. И привожу пример, как может выглядеть в хранимой процедуре вывод результатов в зависимости от вхождения в группу.

IF IS_MEMBER('TEST\CEO')
	SELECT [FirstName], [LastName], [Department], [Salary]
	FROM [HumanResources].[Employee]
ELSE IF IS_MEMBER('TEST\DepartmentHead')
	SELECT [FirstName], [LastName], [Department], [Salary]
	FROM [HumanResources].[Employee]
	WHERE [Department] = (SELECT [Department] FROM [HumanResources].[Employee] WHERE [LoginName] = SUSER_SNAME())
ELSE
	SELECT [FirstName], [LastName], [Department], [Salary]
	FROM [HumanResources].[Employee]
	WHERE [LoginName] = SUSER_SNAME()