- Процедура стала работать медленнее, чем обычно?
- Запрос выполняется быстро, а процедура, в которой подобный запрос, работает очень долго?
- У процедуры неоптимальный план запроса?

Если ответ "Да" хоть на один из вопросов, то эта статья для вас. Я расскажу и покажу, как можно повлиять на работу процедуры и быть уверенным, что в кэше окажется ожидаемый план запроса (а значит никаких больше сюрпризов) для вашей процедуры.

Тема не нова, но если вы это читаете, то моё время потрачено не зря.

Описание проблемы

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

--Тестовая таблица dbo.user_activity_log;
if object_id ( N'dbo.user_activity_log', N'U' ) is not null
  drop table dbo.user_activity_log;
go
create table dbo.user_activity_log ( user_name        varchar(10)
                                   , process_datetime datetime  
                                   );
go

--1000 строк с информацией по пользователю 'User01'
with cte as
(
  select 1 i
  union all
  select i + 1 from cte where i < 1000
)
insert into dbo.user_activity_log ( user_name, process_datetime )
  select 'User01', dateadd( hh, i, '20100101' ) 
    from cte 
    option (maxrecursion 999);

--По одной строке о пользователях 'User02' - 'User09'
insert into dbo.user_activity_log
  values ( 'User02', '20120101' )
       , ( 'User03', '20120101' ) 
       , ( 'User04', '20120101' ) 
       , ( 'User05', '20120101' ) 
       , ( 'User06', '20120101' ) 
       , ( 'User07', '20120101' ) 
       , ( 'User08', '20120101' )
       , ( 'User09', '20120101' );  
go

--Индекс по полю user_name, для иллюстрации различного плана, который выбирает оптимизатор
create index idx_user_name on dbo.user_activity_log ( user_name );
go

Мы знаем, что Оптимизатор запросов использует статистику для оценки количества элементов или количества строк в результате запроса, что позволяет оптимизатору запросов создавать высококачественные планы запросов. Например, на основе оценки количества элементов оптимизатор запросов может выбрать в плане запроса оператор поиска по индексу, а не оператор просмотра индекса, повышая производительность запроса за счет использования менее ресурсоемкого поиска по индексу (http://msdn.microsoft.com/ru-ru/library/ms174384.aspx). Поэтому первым делом обратимся к статистике по нашему индексу:

dbcc show_statistics ( 'dbo.user_activity_log', idx_user_name ) with histogram;

Рис 01. Статистика для индекса idx_user_name

По статистике (на рисунке) видно, что при построении запроса, в котором в качестве условия поиска указан пользователь, в зависимости от входного параметра оптимизатор должен построить разный план. Так, если в качестве пользователя указан User02 - User09, то для более оперативного поиска выгодно использовать поиск по некластерному индексу по полю user_name, но если на входе указан User01, то оптимизатор, скорее всего, выберет полное сканирование таблицы, т.к. необходимо просмотреть много строк таблицы (в нашем примере более 99%) и полный скан будет требовать меньшего количества операций ввода - вывода, и должен выполняться быстрее, чем повторное сканирование не кластерного индекса.

Но это все теория, теперь вернёмся к проблеме, связанной с неоптимальным планом параметризованных процедур.
Создадим примитивную процедуру с одним входным параметром @user_name, которая будет осуществлять выборку из нашей таблицы.

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

create procedure dbo.p_user_activity_log
(
  @user_name varchar(10)
)
as
select * from dbo.user_activity_log
  where user_name = @user_name;
go

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

--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Первый запуск нашей процедуры dbo.p_user_activity_log c пустым входным параметром
exec dbo.p_user_activity_log @user_name = '';

--Просмотр плана для нашей процедуры, который был помещён в кэш
select cacheobjtype, query_plan 
  from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
  where cp.objtype  = 'Proc'
    and qp.objectid = object_id ( N'dbo.p_user_activity_log', N'P' ); 

Рис 02. Графический план запроса

Оптимизатор построил план на основании входного параметра @user_name = ''. Т.к. план строится на основании статистики, то оптимизатор ожидаемо выбрал поиск по некластерному индексу.
Теперь в качестве входного параметра передадим @user_name = 'User02'. Очевидно, что для данного параметра поиск по некластерному индексу будет так же оптимален, т.к. такая запись в таблице одна.

set nocount on;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--и статистику ввода-вывода
set statistics io on;

exec dbo.p_user_activity_log @user_name = 'User02';

Рис 03. Сведения о профиле для инструкции


Рис 04. Статистика ввод-вывода

А теперь посмотрим на те же статистики, но для @user_name = 'User01'.

set nocount on;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--и статистику ввода-вывода
set statistics io on;

exec dbo.p_user_activity_log @user_name = 'User01';

Рис 05. Сведения о профиле для инструкции

Не смотря на то, что для параметра 'User01' выгоднее использовать полное сканирование таблицы, мы опять использовали план из кэша и осуществляли поиск по некластерному индексу. При этом особенно стоит обратить внимание на поля
Rows - Фактическое количество строк, созданных каждым оператором.
EstimateRows - Предполагаемое количество строк вывода от данного оператора.

А теперь статистика ввода-вывода:


Рис 06. Статистика ввод-вывода

Количество чтений просто зашкаливает для нашей небольшой таблицы, их больше 1000(!).
Теперь попробуем снова очистить кэш и выполнить процедуру, но уже с параметром 'User01'. Посмотрим, какой теперь план будет создан и помещён в кэш и другие статистические данные.

set nocount on;

--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--и статистику ввода-вывода
set statistics io on;

--Первый запуск нашей процедуры dbo.p_user_activity_log c входным параметром @user_name = 'User01'
exec dbo.p_user_activity_log @user_name = 'User01';

set statistics profile off;
set statistics io off;

--Просмотр плана для нашей процедуры, который был помещён к вэш
select cacheobjtype, query_plan 
  from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
  where cp.objtype  = 'Proc'
    and qp.objectid = object_id ( N'dbo.p_user_activity_log', N'P' ); 

Сперва план, который попал в кэш:


Рис 07. Графический план запроса

Теперь в кэше план в котором оптимизатор делает полное сканирование таблицы.


Рис 08. Сведения о профиле для инструкции


Рис 09. Статистика ввод-вывода

Теперь у нас всего 4 логических чтения для параметра 'User01', против 1005 при предыдущем плане.
Нетрудно догадаться, что теперь наш план будет не оптимален для других входных параметров.

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

• Изменены таблица или представления, на которые ссылается запрос (ALTER TABLE или ALTER VIEW).
• Изменены индексы, используемые планом выполнения.
• Обновлена статистика, используемая планом выполнения, сформированная либо явным образом по UPDATE STATISTICS, либо автоматически.
• Удалены индексы, используемые планом выполнения.
• Явно вызвана процедура sp_recompile.
• Частое изменение ключей (инструкциями INSERT или DELETE от пользователей, изменяющих таблицу, на которую ссылается запрос).
• Для таблиц с триггерами: значительный рост числа строк в таблицах inserted и deleted.
• Выполнение хранимой процедуры с параметром WITH RECOMPILE.

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

Способы решения

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

Постоянная перекомпиляция (RECOMPILE)

Создание хранимой процедуры с параметром WITH RECOMPILE в определении указывает, что SQL Server не будет кэшировать план этой процедуры; хранимая процедура будет перекомпилироваться при каждом запуске. Параметр WITH RECOMPILE полезен в том случае, когда хранимая процедура принимает параметры, значения которых сильно меняются между выполнениями, что приводит к созданию каждый раз новых планов выполнения. Этот параметр используется редко и замедляет выполнение хранимых процедур, так как они должны перекомпилироваться при каждом запуске.

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

create procedure dbo.p_user_activity_log
(
  @user_name varchar(10)
)
with recompile
as
select * from dbo.user_activity_log
  where user_name = @user_name;
go

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

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

CREATE PROC proc_name 
(
  input_parameters
) 
AS 
Query1
Query2 OPTION(RECOMPILE)
Query3
Query4
Query5 OPTION(RECOMPILE)

GO

В примере выше мы вызываем перекомпиляцию не у всей процедуры, а только для запросов 2 и 5. Таким образом, планы запросов 1,3 и 4 не будут пересоздаваться, а будут браться из кэша.

Использование нескольких процедур

Мы можем сами по входящим параметрам анализировать, какой сценарий нам нужен. Например, для данных в таблице dbo.user_activity_log может быть два сценария: для пользователя User01, т.к. по нему 99% данных в таблице и для всех остальных, тогда наша процедура будет выглядеть примерно так:

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

create procedure dbo.p_user_activity_log
(
  @user_name varchar(10)
)
as
  if @user_name = 'User01'
    exec dbo.p_user_activity_log_1 @user_name
  else 
    exec dbo.p_user_activity_log_2 @user_name
go

Т.е. у нас процедура dbo.p_user_activity_log становится оберткой для анализа входного параметра и дальнейшего вызова новых процедур. Таким образом, у нас будет для 2-х процедур (dbo.p_user_activity_log_1 и dbo.p_user_activity_log_2) два разных плана в кэше, которые будут оптимальными для наших входных параметров. Достаточно универсальный метод для всех версий SQL Server, но, к сожалению, не всегда легко реализуем, если мы не можем предсказать, как наши данные в исходных таблицах будут меняться с течением времени или сценариев не два, как в моём примере, а несколько десятков.

Динамические запросы

Ещё одна хитрость, которой мы можем воспользоваться - это обернуть наш "непредсказуемый" запрос в виде динамического SQL:

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
declare @str nvarchar(max);

set @str = 'select * from dbo.user_activity_log '
         + 'where user_name = ' + quotename( @user_name, '''' );

exec sp_executesql @str;
go

Хитрость заключается в том, что при каждом вызове нашей процедуры в кэш будет помещаться план для конкретного запроса, с конкретным параметром(или браться из кэша, если такой запрос там уже есть):

set nocount on;

--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Вызов процедуры с параметром @user_name = 'User01'
exec dbo.p_user_activity_log @user_name = 'User01';

--Вызов процедуры с параметром @user_name = 'User02'
exec dbo.p_user_activity_log @user_name = 'User02';

--Три вызова процедуры с параметром @user_name = 'User03'
exec dbo.p_user_activity_log @user_name = 'User03';
exec dbo.p_user_activity_log @user_name = 'User03';
exec dbo.p_user_activity_log @user_name = 'User03';

select cacheobjtype
     , objtype
     , text
     , usecounts
     , query_plan 
  from sys.dm_exec_cached_plans cp
    outer apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
    outer apply sys.dm_exec_sql_text ( cp.plan_handle ) st
  where st.text like 'select * from dbo.user_activity_log%'; 

В примере я вызвал нашу процедуру 5 раз, по разу с параметром @user_name = 'User01' и @user_name = 'User02' и три раза с параметром @user_name = 'User03', теперь посмотрим, что у нас в кэше:


Рис 10. Кэшированные планы запросов

В кэше наши запросы фигурируют, как Ad hoc Query-произвольный запрос. При этом в поле usecounts видно, что для параметра @user_name = 'User03' было три случая использования данного объекта кэша с момента его создания, т.е. при повторном запуске процедуры, план брался из кэша. При этом, если посмотреть на графический план для каждого запроса, то мы увидим, что для параметра @user_name = 'User01' в кэше план сканирования таблицы, а для параметров @user_name = 'User02' и @user_name = 'User03' используется поиск по некластерному индексу.

Опция OPTIMIZE FOR

Подсказка, которая появилась в SQL Server 2005, позволяет уменьшить "головную боль" от того, что оптимизатор запросов автоматически использует значения параметра, переданного в хранимую процедуру для определения плана исполнения запроса. Подсказка OPTIMIZE FOR может использоваться для отмены определения параметров по умолчанию в оптимизаторе или при создании структуры плана.
Предположим, что в нашем случае, как правило, процедура dbo.p_user_activity_log будет вызываться с параметром @user_name = 'User01' и для того, чтобы избежать попадания в кэш плана для других входных параметров, который может значительно понизить производительность нашей процедуры мы создадим её с подсказкой OPTIMIZE FOR:

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log 
  where user_name = @user_name
option ( optimize for ( @user_name = 'User01' ) );
go

А теперь вызовем эту процедуру с параметром @user_name = 'User02' и посмотрим, какой план попадёт в кэш:

set nocount on;

--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';

--Просмотр плана для нашей процедуры, который был помещён в кэш
select cacheobjtype, query_plan 
  from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
  where cp.objtype  = 'Proc'
    and qp.objectid = object_id ( N'dbo.p_user_activity_log', N'P' ); 

Благодаря подсказке optimize for, не смотря, на входной параметр, в кэш попал план, в котором оптимизатор использует сканирование всей таблицы, как нам и нужно.

Но, как я сказал выше, подсказка optimize for появилась только в SQL Server 2005, что же делать, если у вас более ранняя версия? Для того чтобы гарантирует в SQL Server 2000 (да и SQL Server 7.0) выбор определенного плана исполнения запроса есть одна хитрость, которая позволяет предотвращать прослушивание параметров и гарантировать лучшей стабильности. Мы используем локальный параметр внутри процедуры, который позволит в момент компиляции выбрать план, который мы хотим (для того параметра, который нам нужен):

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10) 
 ,  @fake_user_name varchar(10) = 'User01'
)
as
--Переопределяем фальшивый параметр для обмана оптимизатора входным параметром
set @fake_user_name = @user_name;

select * from dbo.user_activity_log 
  where user_name = @fake_user_name;
go

Опять смотри план, который попадает в кэш, при вызове процедуры с параметром @user_name = 'User02'

set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;

--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';

Рис 11. Сведения о профиле для инструкции

Таким, достаточно нехитрым методом мы добились поведения аналогичного при подсказке optimize for. Более подробно об этом методе можно прочитать в статье: Методика, которая гарантирует в SQL Server 2000 выбор определенного плана исполнения запроса.

Опция OPTIMIZE FOR UNKNOWN

Выше мы рассмотрели, как можно благодаря подсказке optimize for добиться попадания в кэш гарантированного плана запроса для определённого параметра (-ов), но этот опция не сможет нам помочь, когда наши данные регулярно меняются и мы не можем передать в качестве подсказки конкретное значение. В качестве решения этой проблемы мы можем использовать подсказку, которая появилась в SQL Server 2008 - OPTIMIZE FOR UNKNOWN. Эта подсказка предписывает оптимизатору запросов использовать статистические данные вместо начальных значений для всех локальных переменных, включая параметры, созданные с принудительной параметризацией.
Указав эту опцию в нашей процедуре, мы можем быть уверены, что план запроса будет построен независимо от того, какой параметр будет передан в момент компиляции. В любом случае оптимизатор использует статистику для построения плана, а не наши входные данные.

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log 
  where user_name = @user_name
option ( optimize for ( @user_name unknown ) );
go

А теперь посмотрим, какой план построит оптимизатор для параметра @user_name = 'User02'

set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';

Рис 12. Сведения о профиле для инструкции

Оптимизатор использовал сканирование таблицы, при этом он апеллировал статистическими данными и в поле EstimateRows (Предполагаемое количество строк вывода от данного оператора) значение 112 не случайно. Вернёмся к статистике, по нашему индексу:

dbcc show_statistics ( N'dbo.user_activity_log', idx_user_name );

Рис 13. Статистические данные для индекса idx_user_name

По статистике видно, что общее число строк (Rows) = 1008, а плотность (All Density) «1/различающиеся значения» = 0,111111. Исходя из этих данных оптимизатор и получил число ожидаемых строк = 112 ( Rows * All Density ).

Действительно, в некоторых случаях эта подсказка может оказаться идеальным решение производительности некоторый процедур, но, что делать, если в вашей версии нет подсказки optimize for unknown (повторюсь, появился этот хинт в SQL Server 2008)? Обходное решение есть и для этого случая. Мы опять будем использовать локальные переменные в теле процедуры, чтобы обмануть оптимизатор в момент компиляции плана запроса:

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10) 
)
as
declare @fake_user_name varchar(10);

--Переопределяем фальшивый параметр для обмана оптимизатора входным параметром
set @fake_user_name = @user_name;

select * from dbo.user_activity_log 
  where user_name = @fake_user_name;
go

Ниже информация о профиле для параметра @user_name = 'User02'

set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';

Рис 14. Сведения о профиле для инструкции

Таким образом мы можем эмулировать подсказку optimize for unknown на более ранних версиях.

Принуждаем использовать конкретный индекс

Ещё один из вариантов повлиять на оптимизатор - это "заставить" его всегда использовать тот или иной индекс с помощью ряда подсказок.

INDEX
Синтаксис INDEX(index_value) указывает имя или идентификатор одного или нескольких индексов, используемых при обработке инструкции оптимизатором запросов. Альтернативный синтаксис INDEX = (index_value) допустим только для единичного значения индекса.
Если имеется кластеризованный индекс, аргумент INDEX(0) приводит к просмотру кластеризованного индекса, а INDEX(1) — к просмотру или поиску по кластеризованному индексу. Если кластеризованного индекса нет, аргумент INDEX(0) приводит к просмотру таблицы, а INDEX(1) интерпретируется как ошибка.

FORCESEEK
Указывает, что в качестве пути доступа к данным таблиц или представлений оптимизатор запросов использует только операцию поиска в индексе. Начиная с SQL Server 2008 R2 с пакетом обновления 1 (SP1) могут быть указаны также параметры индекса. Подсказка FORCESEEK с параметрами индекса аналогична использованию FORCESEEK с подсказкой INDEX. Но более эффективного контроля над путем доступа, который использует оптимизатор запросов, можно добиться указанием индекса для поиска и столбцов индекса, которые предполагается использовать в операции поиска.

FORCESCAN
Эта подсказка, впервые появившаяся в SQL Server 2008 R2 с пакетом обновления 1 (SP1), указывает оптимизатору запросов использовать для доступа к указанной таблице или представлению только операцию просмотра индекса. Подсказка FORCESCAN может оказаться полезной в тех запросах, где оптимизатор недооценивает число обрабатываемых строк и выбирает операцию поиска, а не просмотра. В этом случае объем памяти, выделенный для данной операции, будет слишком мал, и это может повлиять на производительность запроса.

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

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log with ( index (0) )
  where user_name = @user_name;
go

Хотелось бы обратить внимание, что если ваши данные не статичны, то такой подход может со временем сказаться на производительности, т.к. через какое-то время полное сканирование может стать причиной замедления работы и вам придётся пересматривать целесообразность подсказки по использованию конкретного индекса.

Форсирование плана

Подсказка в запросе USE PLAN (появилась в SQL Server 2005) применяется, чтобы заставить оптимизатор запросов использовать для формирования запроса определенный план. Подсказка USE PLAN работает посредством приема плана запроса, который необходимо использовать в формате XML в качестве аргумента. Подсказку USE PLAN можно использовать для запросов, планы которых замедляют их выполнение, но для которых существует лучший план.

Всё, что нам нужно, это получить "идеальный" (нужный нам) план для определённых условий и указать его в качестве подсказки в теле нашей процедуры

--Выполним наш запрос с параметром user_name = 'User01' (для получения нужного нам плана)
select * from dbo.user_activity_log 
  where user_name = 'User01';
go

--Вытащим нужный нам план из кэша
declare @xml_showplan nvarchar(max);
select @xml_showplan = query_plan
  from sys.dm_exec_query_stats as qs 
    cross apply sys.dm_exec_sql_text( qs.sql_handle ) as st
    cross apply sys.dm_exec_text_query_plan( qs.plan_handle, default, default ) as qp
  where st.text like N'select * from dbo.user_activity_log%';

select @xml_showplan;
go

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

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log
  where user_name = @user_name
  option ( use plan N'План запроса в виде XML' );
go

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

--Выполним наш запрос с параметром user_name = 'User01' (для получения нужного нам плана)
go
select * from dbo.user_activity_log 
  where user_name = 'User01';
go

--Вытащим нужный нам план из кэша
declare @xml_showplan nvarchar(max);
select @xml_showplan = query_plan
  from sys.dm_exec_query_stats as qs 
    cross apply sys.dm_exec_sql_text( qs.sql_handle ) as st
    cross apply sys.dm_exec_text_query_plan( qs.plan_handle, default, default ) as qp
  where st.text like N'select * from dbo.user_activity_log%';

--Создаем структуру плана 
exec sp_create_plan_guide 
      @name = N'guide_for_p_user_activity_log'
    , @stmt = N'select * from dbo.user_activity_log
                  where user_name = @user_name;'
    , @type = N'OBJECT'
    , @module_or_batch = N'dbo.p_user_activity_log'
    , @params = null
    , @hints = @xml_showplan;
go

--Смотрим, что для нашей процедуры создана структура плана
select * from sys.plan_guides
  where scope_object_id = object_id( N'dbo.p_user_activity_log', N'P' );
go

Наша процедура при этом не содержит никаких подсказок:

if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log
  where user_name = @user_name;
go

Теперь достаточно вызвать нашу процедуру с уже привычным параметром @user_name = 'User02', чтобы убедиться, что план для процедуры выбран нужный нам

set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';

Рис 15. Сведения о профиле для инструкции

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

exec sp_control_plan_guide N'drop', N'guide_for_p_user_activity_log'

Вместо заключения

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

Полезные ссылки по текущей теме

• Кэширование и повторное использование плана выполнения
• Перекомпиляция хранимых процедур
• Подсказки в запросах
• Табличные подсказки
• Методика, которая гарантирует в SQL Server 2000 выбор определенного плана исполнения запроса
• Using the RECOMPILE Query Hint to Solve Parameter Sniffing Problems
• SQL Server parameterization Best Practices
• The Parameter Sniffing Problem
• OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature
• How OPTIMIZE FOR UNKNOWN Works
• Указание планов запросов с помощью форсирования плана

Ещё один вопрос, который регулярно появляется на тематических форумах: "Как получить максимум из нескольких колонок одной таблицы?".
Любую задачу можно решить несколькими способами, эта не исключение. Я попытался собрать несколько вариантов для решения этого вопроса в этой заметке для разных версий SQL Server.
Кроме того, проведу небольшие замеры производительности всех рассмотренных примеров.

Буду рад, если вы предложите ещё несколько вариантов решения этой задачи и с удовольствием дополню эту статью позже.

Начнем с наполнения таблицы тестовыми данными. Для теста я буду использовать небольшую табличку с 5 колонками (a,b,c,d,e). При этом достаточно 5 записей.

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

create table dbo.test_table ( id int identity
                            , a int
                            , b int
                            , c int
                            , d int
                            , e int
                            );
go

insert into dbo.test_table
select 10, 5, 12, 56, 2
union all
select 100, 45, 78, 6, 122
union all
select 150, 50, 127, 596, 22
union all
select 3410, 55, 0, -45, 90
union all
select -10, -5, 0, -56, -2;
go

Рис 01. Таблица с тестовыми данными

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

1. Выражение CASE (любая версия)

select *
     , max_val = case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
                      then a
                      when ( b >= a ) and ( b >= c ) and ( b >= d ) and ( b >= e )
                      then b
                      when ( c >= a ) and ( c >= b ) and ( c >= d ) and ( c >= e )
                      then c
                      when ( d >= a ) and ( d >= b ) and ( d >= c ) and ( d >= e )
                      then d
                      else e
                 end 
  from dbo.test_table

2. Подзапрос и оператор UNION (любая версия)

select *
     , max_val = ( select max(i) 
                     from ( select a 
                            union all 
                            select b 
                            union all 
                            select c
                            union all 
                            select d
                            union all 
                            select e
                          ) t(i)
                 )
  from dbo.test_table

3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)

select *
     , max_val = ( select max(i) 
                     from ( values (a)
                                  ,(b)
                                  ,(c)
                                  ,(d)
                                  ,(e) 
                          ) t(i)
                 )
  from dbo.test_table

4. Оператор UNPIVOT (SQL Server 2005 и выше)

select id, max(val) as max_val
  from ( select * 
          from dbo.test_table t
            unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
       
       ) t
  group by id

5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)

select top 1 with ties id, val 
  from dbo.test_table t
    unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
  order by row_number() over ( partition by id order by val desc )

6. Оператор CROSS APPLY (SQL Server 2005 и выше)

select * 
  from dbo.test_table t
    cross apply ( select top 1 val 
	                  from ( values (a),(b),(c),(d),(e) ) t (val)
                    order by val desc
                ) ca (max_val)

--Ещё вариант

select id, a, b, c, d, e, max_val
  from dbo.test_table t
    cross apply ( select row_number() over (order by val desc) i, val
	                  from ( values (a),(b),(c),(d),(e) ) t (val)
                ) ca (i, max_val)
  where ca.i = 1

7. Inline-функция и CTE (SQL Server 2005 и выше)

if object_id ( N'dbo.f_get_max', N'IF' ) is not null
  drop function dbo.f_get_max;
go
create function dbo.f_get_max ( 
    @a int
  , @b int
  , @c int
  , @d int
  , @e int
) returns table
as
return
with cte
as
( select val from 
    ( values (@a),(@b),(@c),(@d),(@e) 
    ) t(val)
)
select max(val) as max_val from cte;
go 

select * from dbo.test_table t
  cross apply dbo.f_get_max ( a, b, c, d, e )
go

А теперь посмотрим на производительность этих запросов, чтобы определить, какой из вариантов более предпочтителен. Тест буду проводить так же на 5 колонках таблицы с 10 млн. записей.

set nocount on;
go

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

create table dbo.test_table ( id int identity primary key clustered
                            , a int
                            , b int
                            , c int
                            , d int
                            , e int
                            );
go

--Генерим 10 млн строк
with cte 
as
( 
  select 1 i
  union all
  select i+1 from cte where i < 10000000
)
insert into dbo.test_table
select checksum ( newid() ) % 1000 as a
     , checksum ( newid() ) % 1000 as b
     , checksum ( newid() ) % 1000 as c
     , checksum ( newid() ) % 1000 as d
     , checksum ( newid() ) % 1000 as e
  from cte
option ( maxrecursion 0 );

declare @val int;

print '1. Выражение CASE (любая версия)';
set statistics time on;

select @val =  case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
                    then a
                    when ( b >= a ) and ( b >= c ) and ( b >= d ) and ( b >= e )
                    then b
                    when ( c >= a ) and ( c >= b ) and ( c >= d ) and ( c >= e )
                    then c
                    when ( d >= a ) and ( d >= b ) and ( d >= c ) and ( d >= e )
                    then d
                    else e
                 end 
  from dbo.test_table;

set statistics time off;

print '2. Подзапрос и оператор UNION (любая версия)';
set statistics time on;

select @val = ( select max(i) 
                     from ( select a 
                            union all 
                            select b 
                            union all 
                            select c
                            union all 
                            select d
                            union all 
                            select e
                          ) t(i)
                 )
  from dbo.test_table;

set statistics time off;

print '3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)';
set statistics time on;

select @val = ( select max(i) 
                  from ( values (a)
                              ,(b)
                              ,(c)
                              ,(d)
                              ,(e) 
                      ) t(i)
              )
  from dbo.test_table;

set statistics time off;

print '4. Оператор UNPIVOT (SQL Server 2005 и выше)';
set statistics time on;

select @val = max(val)
  from ( select * 
          from dbo.test_table t
            unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
       
       ) t
  group by id;

set statistics time off;

print '5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)';
set statistics time on;

select top 1 with ties @val = val 
  from dbo.test_table t
    unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
  order by row_number() over ( partition by id order by val desc );

set statistics time off;

print '6. Оператор CROSS APPLY (SQL Server 2005 и выше)';

print 'CROSS APPLY 1';
set statistics time on;
select @val = max_val 
  from dbo.test_table t
    cross apply ( select top 1 val 
	                  from ( values (a),(b),(c),(d),(e) ) t (val)
                    order by val desc
                ) ca (max_val);

print 'CROSS APPLY 2';
select @val = max_val
  from dbo.test_table t
    cross apply ( select row_number() over (order by val desc) i, val
	                  from ( values (a),(b),(c),(d),(e) ) t (val)
                ) ca (i, max_val)
  where ca.i = 1;

set statistics time off;

print '7. Inline-функция и CTE (SQL Server 2005 и выше)';
set statistics time on;

select @val = max_val from dbo.test_table t  
  cross apply dbo.f_get_max ( a, b, c, d, e );

set statistics time off;
go

Результат ниже:

1. Выражение CASE (любая версия)

SQL Server Execution Times:
   CPU time = 3416 ms,  elapsed time = 3416 ms.
----------------------------------------------------------------------

2. Подзапрос и оператор UNION (любая версия)

SQL Server Execution Times:
   CPU time = 12777 ms,  elapsed time = 3848 ms.
----------------------------------------------------------------------

3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)

SQL Server Execution Times:
   CPU time = 13182 ms,  elapsed time = 3968 ms.
----------------------------------------------------------------------

4. Оператор UNPIVOT (SQL Server 2005 и выше)

SQL Server Execution Times:
   CPU time = 22246 ms,  elapsed time = 15966 ms.
----------------------------------------------------------------------

5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)

SQL Server Execution Times:
   CPU time = 153957 ms,  elapsed time = 239071 ms.
----------------------------------------------------------------------

6. Оператор CROSS APPLY (SQL Server 2005 и выше)

CROSS APPLY 1

SQL Server Execution Times:
   CPU time = 22963 ms,  elapsed time = 6273 ms.

CROSS APPLY 2

SQL Server Execution Times:
   CPU time = 33244 ms,  elapsed time = 9270 ms.
----------------------------------------------------------------------

7. Inline-функция и CTE (SQL Server 2005 и выше)

SQL Server Execution Times:
   CPU time = 12246 ms,  elapsed time = 3787 ms.

Собственно проблема озвучена в тебе этого поста. Если у вас на рабочей станции при двойном клике по файлам с расширением *.sql загружается новый экземпляр SSMS (SQL Server Management Studio), вместо того, чтобы открыть файл в новой вкладке уже открытой студии, то эта заметка для вас.

Решение простое, достаточно поправить один из параметров реестра.

• 1) Открываем ветку реестра HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\Command
• 2) Правим параметр по умолчанию с значением "c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" /dde
• 3) Добавляем в конец параметра значение "%1"
• 4) В результате значение должно быть "c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" /dde "%1"

Данное решение подходит для SQL Server Management Studio 2005/2008/2008R2 (меняется только путь до ssms.exe в значении параметра).

В SSMS 2012 ветка реестра уже другая: HKEY_CLASSES_ROOT\ssms.sql.11.0\Shell\Open\Command

1024x640 - 1024x768

Ещё немного обоев SQL Server

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

Существует два вида временных таблиц: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных таблиц начинаются с одного символа (#); они видны только текущему соединению пользователя и удаляются, когда пользователь отключается от экземпляра SQL Server. Имена глобальных таблиц начинаются с двух символов номера (##); они видны любому пользователю и удаляются, когда все пользователи, которые на них ссылаются, отключаются от экземпляра SQL Server.

Например, если создать таблицу employees, она будет доступна любому пользователю, которому предоставлены разрешения на ее использование до тех пор, пока не будет удалена. Если во время сеанса базы данных создается локальная временная таблица #employees, с ней сможет работать только данный сеанс. Таблица будет удалена при завершении сеанса. Если создать глобальную временную таблицу ##employees, с ней сможет работать любой пользователь базы данных. Если другие пользователи не будут работать с этой таблицей, она будет удалена после отключения от нее. Если другой пользователь обратится к созданной таблице, SQL Server удалит ее, когда произойдет отключение и другие сеансы перестанут активно к ней обращаться.

А теперь выдержка из BOL, которой я коснусь в этой статье:
Если локальная временная таблица создается хранимой процедурой или приложением, которые одновременно могут выполняться несколькими пользователями, компонент Database Engine должен иметь возможность различать таблицы, созданные разными пользователями. Компонент Database Engine делает это путем внутреннего присоединения числового суффикса к имени каждой локальной временной таблицы. Полное имя временной таблицы, хранящееся в таблице sysobjects базы данных tempdb, состоит из имени таблицы, заданного инструкцией CREATE TABLE, и сформированного системой числового суффикса. Для обеспечения возможности добавления суффикса значение параметра table_name, определенного как имя локальной временной таблицы, не должно содержать более 116 символов.

Действительно, если мы создадим временную таблицу и посмотрим её "ностоящее" наименование в tempdb, то мы увидем совсем не то имя, которое ей давали:

use master
go 
create table #t ( id int, val1 varchar(10), val2 datetime ) 
go 
select top 1 * from tempdb.sys.tables 
order by create_date desc 
go

Как видно на рисунке, таблица создана с уникальным именем. Не смотря на то, что у таблицы такое странное наименование, мы можем обращаться к этой таблице по короткому имени, по тому с которым мы её создали. При этом из другого сеанса к этой таблице нельзя обращаться ни по короткому ни по полному имени, т.к. для другой сеанса наша таблица находится вне области видимости. При этом в другом сеансе можно спокойно создать таблицу с тем же именем не переживая, что сиквел выругается на то, что такой объект уже существует в БД. Но, как оказывается, не всё так гладко. Компонент Database Engine создаёт уникальное имя только для самой таблицы, а для констрейнтов этой таблицы в tempdb уникальность наименования не поддерживается. Для иллюстрации описанной проблемы, немного изменим предыдущий скрипт создания временной таблицы:

use master
go 
create table #t ( id int, val1 varchar(10), val2 datetime constraint df_dt default getdate() ) 
go 
select top 1 * from tempdb.sys.tables 
order by create_date desc 
go

И теперь если запустить этот код в двух разных сеансах, то в первом код успешно отработает:

А во втором получим ошибку

Msg 2714, Level 16, State 5, Line 1 There is already an object named 'df_dt' in the database.
Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.

Констрейнт имеет име 'df_dt', т.е. мы не получили уникальность, как это происходит с именем таблицы. Именно поэтому мы и увидели столь неочевидную ошибку.

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

Продолжим наши эксперименты и попробуем вставить код создания таблицы в процедуру:

if object_id( 'dbo.Test_Procedure', 'P' ) is not null
drop procedure dbo.Test_Procedure
go
create procedure dbo.Test_Procedure
as
create table #t ( id int, val1 varchar(10), val2 datetime constraint df_dt default getdate() )
go

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

exec dbo.Test_Procedure
go
select top 2 * from tempdb.sys.objects 
order by create_date desc
go

При этом мы не увидим нашей таблицы среди объектов tempdb. Но если запустить вызов процедуры в транзакции, то мы получим совершенно другую картину:

begin tran
go
exec dbo.Test_Procedure
go
select top 2 * from tempdb.sys.objects 
order by create_date desc
go

Т.е. наши объекты не были автоматически удалены при выходе из процедуры и они "живут" в tempdb до завершения транзакции. При этом обратиться к нашей таблице из вне процедуры не выйдет, т.к. область видимости ограничивается телом процедуры. Теперь, если мы решим запустить нашу процедуру в одном сеансе дважды в транзакции:

begin tran
go
exec dbo.Test_Procedure
go
exec dbo.Test_Procedure
go

То опять получим ошибку, связанную с тем, что имя констрейнта не уникально:

Msg 2714, Level 16, State 5, Procedure Test_Procedure, Line 3 There is already an object named 'df_dt' in the database.
Msg 1750, Level 16, State 0, Procedure Test_Procedure, Line 3 Could not create constraint. See previous errors.

Мы знаем, что при удалении таблицы, удалятся и все её дочерние объекты (констрейнты, триггеры, индексы и т.д.). Казалось бы логично перед созданием таблицы проверять факт её наличия в БД и предварительно её удалять, чтоб удалились и все связанные с ней объекты. Но с временной таблицей этот номер не выйдет из-за её особенности создавать уникальное име в tempdb.

Но это ещё не всё, теперь попробуем запустить нашу процедуру в транзакции, но в разных сеансах.

begin tran
go
exec dbo.Test_Procedure
go

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

--Первый сеанс
exec sp_lock 52
--Второй сеанс
exec sp_lock 53

Причиной блокировки стал наш констрейнт, а т.к. блокировки Sch-M и Sch-S не совместимы( http://msdn.microsoft.com/ru-ru/library/ms186396.aspx), то второй сеанс ожидает высвобождения ресурсов.

Именно из-за своей неочевидности эти особенности работы с локальными временными таблицами заслуживают внимания. Проблему можно решить несколькими способами:
1) Удалять временные объекты, перед выходом из процедуры
2) Не создавать явное наименование для констрейнтов на временных таблицах, тогда наш код создания таблицы можно изменить на указанный ниже, и сиквел сам сгенерит своё уникальное имя для объектов:

use master
go 
create table #t ( id int, val1 varchar(10), val2 datetime default getdate() ) 
go 
select top 2 * from tempdb.sys.objects
order by create_date desc  
go

Была даже идея оформить фидбэк, но это уже сделали до меня...ещё в далёком 2007-ом году (http://connect.microsoft.com/SQLServer/feedback/details/250046/temporary-tables-with-named-constraints). Официальный ответ: by design, но возможно в будущих релизах ситуация изменится.

Тесты я проводил на

Microsoft SQL Server "Denali" (CTP3) - 11.0.1440.19 (X64) 
	Jun 24 2011 17:31:09 
	Copyright (c) Microsoft Corporation
	Enterprise Evaluation Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Сомневаюсь, что в ближайшее время ситуация изменится, поэтому будте внимательны при работе с временными таблицами!!!

Агрегатные (статические) функции выполняют вычисление на наборе значений и возвращают одиночное значение. Статистические функции, за исключением COUNT, не учитывают значения NULL. Статистические функции часто используются в выражении GROUP BY инструкции SELECT.

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

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

• Список выбора инструкции SELECT (вложенный или внешний запрос).
• Предложение COMPUTE или COMPUTE BY.
• Предложение HAVING.

Transact-SQL предоставляет следующие статистические функции:

• AVG
• CHECKSUM_AGG
• COUNT
• COUNT_BIG
• GROUPING
• MAX
• MIN
• SUM
• STDEV
• STDEVP
• VAR
• VARP

К сожалению это весь список, но что делать, когда нам нужна агрегатная функция, которой нет в T-SQL? В этой статье я покажу, как создать свою агрегатную функцию на примере побитового OR (Побитовое ИЛИ). Варианты решения будут в виде классического t-sql и в виде CLR-сборки. Кроме демонстрации скриптов, проведу небольшие замеры и сравнения производительности вариантов на t-sql и clr.

Побитовое ИЛИ (OR) — это бинарная операция, действие которой эквивалентно применению логического ИЛИ к каждой паре битов, которые стоят на одинаковых позициях в двоичных представлениях операндов. Другими словами, если оба соответствующих бита операндов равны 0, двоичный разряд результата равен 0; если же хотя бы один бит из пары равен 1, двоичный разряд результата равен 1.

Пример:

ИЛИ	0011
	0101
=	0111

| (Побитовое ИЛИ) (Transact-SQL)
Выполняет поразрядную логическую операцию OR для двух указанных целочисленных значений, которые преобразуются в двоичные выражения в инструкциях Transact-SQL.



Проблема возникает, когда нужна агрегатная функция побитового ИЛИ. Стандартной агрегатной функции нет, поэтому будем писать свой скрипт. Для демонстрации создадим таблицу с тестовыми данными.

--Создадим тестовую таблицу
if object_id ( 'dbo.TestTable', 'U' ) is not null
  drop table dbo.TestTable
go
create table dbo.TestTable ( id int identity, group_id int, bitwise int )
go
--Сгенерим данные: 1 млн. групп, в каждой группе по 3 значения, над которыми мы и будем выполнять агрегирующее ИЛИ 
with bitwise
as
(
select 0 as bit
union all
select bit + 1 from bitwise b
  where bit < 100
), temp_groups
as
(
select 0 as group_id
union all
select group_id + 1 from temp_groups b
  where group_id < 1000000
)
insert into dbo.TestTable
  select group_id, bit 
    from temp_groups t 
      cross apply ( select top (3) bit 
                      from bitwise 
                      where bit != group_id 
                      order by newid () 
                  ) ca
  option ( maxrecursion 0 )

--Добавим индекс
if exists ( select * from sys.indexes
              where object_id = object_id ( 'dbo.TestTable', 'U' ) 
                and name = 'cind_gr'
          )
drop index dbo.TestTable.cind_gr
go

create clustered index cind_gr on dbo.TestTable ( group_id )
go

А теперь 2 варианта на T-SQL, как можно выполнить агрегирующее побитовое ИЛИ.

Вариант 1. Используем PIVOT, для выстраивания списка полей в строку, а дальше используем стандартную операцию побитового или |

select group_id
     , isnull( ( select isnull( [1], 0 )    | isnull( [2], 0 )    | isnull( [4] ,0 )    
                      | isnull( [8] ,0 )    | isnull( [16], 0 ) 
                      | isnull( [32], 0 )   | isnull( [64], 0 )   | isnull( [128] ,0 )  
                      | isnull( [256] ,0 )  | isnull( [512], 0 )
                      | isnull( [1024], 0 ) | isnull( [2048], 0 ) | isnull( [4096] ,0 )
                      | isnull( [8192] ,0 ) | isnull( [16384], 0 )
                      | isnull( [32768], 0 )
           from ( select dgr.i 
                    from ( select bitwise i 
                            from dbo.TestTable where group_id = t.group_id
                         ) dsc
                      inner join ( values (1),(2),(4),(8),(16),(32),(64),(128),(256),(512),(1024),(2048),(4096),(8192),(16384),(32768) ) dgr(i) 
                        on dsc.i & dgr.i = dgr.i
             ) a(i)
        pivot ( max(i) for i in ( [1],[2],[4],[8],[16],[32],[64],[128],[256],[512],[1024],[2048],[4096],[8192],[16384],[32768] ) ) pv 
       ), 0 ) as AggOr
  from dbo.TestTable t
  group by group_id
  order by group_id

Вариант 2. Небольшая хитрость в виде стандартной агрегатной функции MAX и побитовой операции И (&)

select group_id
     , max( bitwise&32768 ) + max( bitwise&16384 ) + max( bitwise&8192 ) + max( bitwise&4096 ) 
     + max( bitwise&2048 ) + max( bitwise&1024 ) + max( bitwise&512 ) + max( bitwise&256 ) 
     + max( bitwise&128 ) + max( bitwise&64 ) + max( bitwise&32 ) + max( bitwise&16 ) 
     + max( bitwise&8 ) + max( bitwise&4 ) + max( bitwise&2 ) + max( bitwise&1 ) as AggOR 
  from dbo.TestTable
  group by group_id
  order by group_id

Вариант 3. Создадим свою агрегатную функцию CLR

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct AggOR
{
    private SqlInt32 AggDiscounts;
    public void Init()
    {
        AggDiscounts = 0;
    }

    public void Accumulate(SqlInt32 Value)
    {
        AggDiscounts |= Value;
    }

    public void Merge(AggOR Group)
    {
        AggDiscounts |= Group.AggDiscounts;
    }

    public SqlInt32 Terminate()
    {
        return AggDiscounts;
    }

}

Подключаем сборку:

exec sp_configure 'clr enabled', 1
reconfigure
go

alter database TestDB set Trustworthy on
go

if object_id( 'dbo.AggOr', 'AF' ) is not null
  drop aggregate AggOr
go
if exists ( select * from sys.assemblies
              where name = 'AggOr_Assemblie' )
  drop assembly AggOr_Assemblie
go

create assembly AggOr_Assemblie
from 'C:\temp\AggOR.dll'
go
 
create aggregate dbo.AggOr ( @bitwise int )
returns int
external name AggOr_Assemblie.AggOR
go

Ну и собственно сам вариант работы с нашей новой агрегатной функцией:

select group_id
     , dbo.AggOr (bitwise) as AggOR 
  from dbo.TestTable
  group by group_id
  order by group_id

А теперь несколько обещанных сравнений этих трёх запросов:


План запроса 1



План запроса 2



План запроса 3



Дальше больше, теперь общая статистика по времени и по IO:


Статистика запроса 1



Статистика запроса 2



Статистика запроса 3


Как видно из статистик, 1 запрос самый тяжёлый и по времени и по количеству чтений, 2 и 3 запросы выглядят на много лучше, но у 3-его время работы самое лучшее, хоть он и более требовательный к CPU.

ЗЫ: Выкладываю CLR-сборку агрегатной функции побитового ИЛИ - AggOR.dll (4,00 kb)

В SQL Server есть ограничение на инструкцию INSERT EXEC - она не может быть вложенной. Т.е. если в теле процедуры мы уже используем код INSERT EXEC, то рекордсет из этой процедуры мы не сможем вставить в таблицу. На Microsoft Connect есть фитбек с этой проблемой (Cannot have nested INSERT ... EXEC) и совсем недавно эту проблему закрыли с пометкой as Won't Fix.

Но, что делать, если нам все-таки необходимо вывести результат работы процедуры в таблицу? Именно тому, как обойти одно из ограничений сиквела и посвящён этот пост.

Для иллюстрации создадим тестовую БД TestDB и две процедуры, одна будет возвращать небольшой рекордсет, а во второй мы будем вызывать эту процедуру с использованием инструкции INSERT EXEC.

create database TestDB
go

use TestDB
go

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

create procedure dbo.TestProc01
as
set nocount on
declare @t table ( i int )
insert into @t
values (1),(2),(3)
select * from @t
go

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

create procedure dbo.TestProc02
as
set nocount on
declare @t table ( i int )
insert into @t
exec dbo.TestProc01
select * from @t
go

Дальше небольшой скрипт, который и эмулирует, озвученную выше, ошибку:

declare @t table ( i int )
insert into @t
exec dbo.TestProc02

А теперь несколько способов обойти эту ошибку:

1) Первый и самый правильный
По возможности не использовать вложенных инструкций INSERT EXEC, либо вместо вызова процедуры, в которой уже используется такая конструкция, взять часть скрипта из тела этой самой процедуры. Как правило, это достаточно легко можно реализовать. В моём примере достаточно заменить вызов процедуры dbo.TestProc02 на dbo.TestProc01.

2) Используем OPENQUERY или OPENROWSET
Для этого нам потребуется создать Linked Server с ссылкой нашего сервера БД на самого себя ( в моём случае это IP 127.0.0.1).

use master
go

exec sp_addlinkedserver N'127.0.0.1'
                      , N'SQL Server';
go

use TestDB
go

declare @t table ( i int )
insert into @t
select * from OpenQuery ( [127.0.0.1], 'TestDB.dbo.TestProc02' )
select * from @t

3) Используем распределённый запрос

declare @t table ( i int )
insert into @t
exec [127.0.0.1].TestDB.dbo.TestProc02
select * from @t
go
--либо:
declare @t table ( i int )
insert into @t
exec ( 'TestDB.dbo.TestProc02' ) at [127.0.0.1]
select * from @t
go

Не забываем включить службу Координатор распределенных транзакций Иначе получим ошибку:



4) Используем процедуру xp_cmdshell и утилиту SQLCMD
Но для начала включим использование процедуры xp_cmdshell

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
go

А теперь выгружаем результат работы процедуры dbo.TestProc02 в таблицу:

declare @t table ( val varchar(100) )
insert into @t
exec master..xp_cmdshell 'sqlcmd -E -q "exec TestDB.dbo.TestProc02" -h -1 -W'

select val from @t
where val is not null

5) Используем процедуру xp_cmdshell и утилиту BCP

--Выгружаем результат процедуры dbo.TestProc02 на диск
exec xp_cmdshell 'bcp "exec TestDB.dbo.TestProc02" queryout "c:\temp\Test.txt" -T -c -C RAW -r\n -t\char(3)'
--Создадим таблицу для получения результата 
if object_id ( 'dbo.tmpMyResult', 'U' ) is not null
drop table tmpMyResult 
go 
create table tmpMyResult ( val int )
--Загружаем результат с диска в таблицу
exec xp_cmdshell 'bcp TestDB.dbo.tmpMyResult in "c:\temp\Test.txt" -T -c -C RAW -r\n -t\char(3)'  
--Смотрим
select * from tmpMyResult

6) Используем CLR
Но этот вариант я не буду рассматривать в рамках этого поста.

Постраничная выборка, пейджинг - это достаточно популярная тема/задача, которую приходится решать любому программисту. Очень многие ругали SQL Server, за то, что в отличии от других СУБД, в сиквеле эта задача решается не так прозрачно. Для решения этой задачи приходилось использовать подзапросы с несколькими операторами TOP, либо ранжирующие функции, которые накладывают большие сопутствующие затраты, что сказывается на производительности.

В новой версии SQL Server эта задача решается с помощью предложений OFFSET и FETCH.
Применение в качестве решения для разбиения на страницы предложений OFFSET и FETCH потребует однократного выполнения запроса для каждой «страницы» данных, возвращаемых клиентскому приложению. Например, чтобы вернуть результаты запроса блоками по 10 строк, необходимо выполнить запрос для получения строк с 1 по 10, затем еще раз для получения строк с 11 по 20 и так далее. Каждый запрос выполняется независимо и никаким образом не связан с другими запросами.

Для тестов создадим таблицу с 10 млн записей и сравним 3 запроса:

• 1) решение через оператор TOP (вариант для SQL Server 2000 и ниже)
• 2) решение через ранжирующую функцию ROW_NUMBER (вариант для SQL Server 2005 и выше)
• 3) решение через предложения OFFSET и FETCH (вариант для SQL Server 2011)

 

if object_id( 'dbo.Table01', 'U' ) is not null
drop table dbo.Table01
go
--Таблица для тестов
create table dbo.Table01 ( val uniqueidentifier )
go
--Наполняем тестовыми данными - 10 млн строк
with cte ( id, val )
as
( select 1, newid()
    union all
  select id + 1, newid()
    from cte 
    where id < 10000000
)
insert into dbo.Table01
select val from cte
option ( maxrecursion 0 )
go

--Создадим индекс
create index idx on dbo.Table01 ( val )
go

Теперь 3 запроса на выборку записей с 100001 по 100500 упорядоченные по текстовому полю val

--1) выборка с помощью оператора TOP
select * from
  (
  select top 500 * from
      (
      select top 100500 * from dbo.Table01
      order by val
      ) t
    order by val desc
  ) t
order by val

--2) выборка с помощью ранжирующей функции ROW_NUMBER
select val from
(
select row_number() over ( order by val ) id, * from dbo.Table01
) t 
where id between 100001 and 100500

--3) вариант с использованием OFFSET и FETCH
select * from dbo.Table01
  order by val 
    offset 100000 rows     
    fetch next 500 rows only

А теперь результат сравнения этих 3х запросов по времени и по IO ( set statistics io on; set statistics time on; ). Для чистоты эксперимента, перед каждым запросом очищаю буферный пул ( dbcc dropcleanbuffers ):

Первый запрос:



Второй запрос:



Третий запрос:






Ссылки по теме:

• http://msdn.microsoft.com/ru-ru/library/ms188385(v=sql.110).aspx
• http://reznik.uneta.com.ua/post/2011/07/30/sql-server-denali-paging.aspx
• http://blog.sqlauthority.com/2010/12/16/sql-server-server-side-paging-in-sql-server-2011-performance-comparison/
• http://www.sql.ru/faq/faq_topic.aspx?fid=105

В SQL:2003 появилась возможность определения нового вида объектов базы данных – генераторов последовательностей (sequence generators). Такого рода объекты производят изменяемые во времени точные числовые значения. Генераторы последовательностей могут оказаться полезными в разных контекстах среды SQL. При этом они уже давно присутствуют в большинстве реляционных базах данных таких, как Oracle, DB2, PostgreSQL и т.д.

В новой версии SQL Server под кодовым названием Denali (SQL Server 2011) так же появились Sequence (последовательности). Последовательность представляет собой определяемый пользователем объект, привязанный к схеме, который формирует последовательность числовых значений в соответствии со спецификацией, с которой эта последовательность создавалась. Последовательность числовых значений формируется в возрастающем или убывающем порядке с заданным интервалом; можно настроить перезапуск (зацикливание) последовательности, когда она исчерпана. В отличие от столбцов идентификаторов последовательности не связаны с конкретными таблицами. Приложение обращается к объекту последовательности, чтобы получить следующее значение. Приложения управляют связями между последовательностями и таблицами. Пользовательские приложения могут ссылаться на объект последовательности и распределять значения между несколькими строками и таблицами.

Синтаксис команды:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH  ]
    [ INCREMENT BY  ]
    [ { MINVALUE [  ] } | { NO MINVALUE } ]
    [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [  ] } | { NO CACHE } ]
    [ ; ]

Параметры:

sequence_name
Указывает уникальное имя, под которым последовательность известна в базе данных. Тип sysname.

[ built_in_integer_type | user-defined_integer_type
Последовательность может быть определена с любым целочисленным типом. Допускаются следующие типы.

• tinyint — диапазон от 0 до 255


• smallint — диапазон от -32 768 до 32 767


• int — диапазон от -2 147 483 648 до 2 147 483 647


• bigint — диапазон от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807


• decimal и numeric с масштабом 0.


• Любой определяемый пользователем тип данных (псевдоним типа), основанный на одном из допустимых типов.

Если тип данных не указан, то по умолчанию используется тип bigint.

START WITH
Первое значение, возвращаемое объектом последовательности. Значение START должно быть не больше максимального и не меньше минимального значения объекта последовательности. По умолчанию начальным значением для нового объекта последовательности служит минимальное значение для объекта возрастающей последовательности и максимальное — для объекта убывающей.

INCREMENT BY <константа>
Значение, на которое увеличивается (или уменьшается, если оно отрицательное) значение объекта последовательности при каждом вызове функции NEXT VALUE FOR. Если значение приращения отрицательно, то объект последовательности убывает, в противном случае — возрастает. Приращение не может быть равно 0. По умолчанию для нового объекта последовательности используется приращение 1.

[ MINVALUE | NO MINVALUE ]
Указывает граничные значения для объекта последовательности. По умолчанию минимальным значением для нового объекта последовательности служит минимальное значение для типа данных объекта последовательности. Для типа данных tinyint это ноль, для всех остальных типов данных — отрицательное число.

[ MAXVALUE | NO MAXVALUE
Указывает граничные значения для объекта последовательности. По умолчанию максимальным значением для нового объекта последовательности служит максимальное значение для типа данных объекта последовательности.

[ CYCLE | NO CYCLE ]
Свойство, которое указывает, перезапускается объект последовательности с минимального значения (или максимального для объектов убывающих последовательностей) или вызывает исключение, когда достигнуто максимальное (или максимальное) значение. По умолчанию для новых объектов последовательности используется параметр цикличности NO CYCLE.
Учтите, что циклическое повторение начинается не с начального, а с минимального или максимального значения.

[ CACHE [ ] | NO CACHE ]
Повышает производительность для приложений, использующих объекты последовательностей, сводя к минимуму число операций дискового ввода-вывода, которые требуются для создания порядковых номеров.
Например, если выбран размер кэша 50, то SQL Server не кэширует 50 отдельных значений. Кэшируется только текущее значение и число значений, оставшихся в кэше. Это значит, что объем памяти для хранения кэша всегда равен размеру двух экземпляров типа данных объекта последовательности.
Если создание проводилось с параметром CACHE, то непредвиденное завершение работы (например, сбой электропитания) может привести к потере порядковых номеров, оставшихся в кэше.

Последовательность можно создать не только с помощью скрипта на T-SQL, но и через SQL Server Management Studio (SSMS):



При двойном клике по Sequence или при выборе пункта меню "New Sequence...", открывается окно в котором мы так же можем задать все параметры для последовательности:



Теперь попробуем пройтись по всем параметрам (аргументам) sequence более подробно.
Минимальная и достаточная инструкция для создания последовательности:

create sequence dbo.seq01

Информацию о всех последовательностях БД можно просматривать с помощью системного представления sys.sequences
Первые 12 полей этого представления полностью дублируют поля другого системного представления sys.objects. Тут же отмечу, что новый объект БД sequence имеет краткое обозначение - SO (SEQUENCE_OBJECT). Ниже проиллюстрирована выборка из sys.sequences (только поля, которые хранят параметры нашей последовательности):

• start_value - Стартовое значение для объекта последовательности. Если объект последовательности перезапускается с помощью инструкции ALTER SEQUENCE, он начинается с этого значения. Когда объект последовательности выходит на следующий круг, он начинается с minimum_value или maximum_value, а не с start_value.
• increment - Значение, на которое увеличивается значение объекта последовательности после каждого созданного значения.
• minimum_value - Минимальное значение, возвращаемое объектом последовательности. По достижении этого значения объект последовательности либо возвращает ошибку при попытке создать дополнительные значения, либо перезапускается, если для него указан параметр CYCLE. Если параметр MINVALUE не задан, этот столбец возвращает минимальное значение, допустимое типом данных генератора последовательности.
• maximum_value - Максимальное значение, возвращаемое объектом последовательности. По достижении этого значения объект последовательности либо начинает возвращать ошибку при попытке создать дополнительные значения, либо перезапускается, если для него указан параметр CYCLE. Если параметр MAXVALUE не задан, этот столбец возвращает максимальное значение, допустимое типом данных объекта последовательности.
• is_cycling - Возвращает значение 0, если для объекта последовательности указан параметр NO CYCLE, и 1, если указан параметр CYCLE.
• is_cached - Возвращает значение 0, если для объекта последовательности указан параметр NO CACHE, и 1, если указан параметр CACHE.
• cache_size - Возвращает заданный размер кэша для объекта последовательности. Этот столбец содержит значение NULL, если последовательность была создана с параметром NO CACHE или был указан параметр CACHE без указания размера кэша. Если значение cache_size больше максимального числа значений, которые может возвращать объект последовательности, все равно показывается такой недостижимый размер кэша.
• system_type_id - Идентификатор системного типа для типа данных объекта последовательности.
• user_type_id - Определенный пользователем идентификатор типа данных для объекта последовательности.
• precision - Максимальная точность типа данных.
• scale - Максимальный масштаб типа данных. Масштаб возвращается вместе с точностью для предоставления пользователю полных метаданных.Масштаб объектов последовательности всегда равен 0, поскольку для них допустимы только целочисленные типы.
• current_value - Последнее предоставленное значение. Это значение, возвращенное в результате последнего выполнения функции NEXT VALUE FOR, или последнее значение при выполнении процедуры sp_sequence_get_range. Если последовательность не использовалась, возвращается значение START WITH.
• is_exhausted - 0 указывает, что последовательность еще может предоставлять новые значения. 1 указывает, что объект последовательности достиг значения MAXVALUE и для последовательности не задан параметр CYCLE. Функция NEXT VALUE FOR будет возвращать ошибку, пока последовательность не будет перезапущена с помощью инструкции ALTER SEQUENCE.

Создадим последовательность указав явный тип tinyint

create sequence dbo.seq02 as tinyint

т.к. tinyint - это диапазон от 0 до 255, то наша последовательность будет, как раз в этом диапазоне.

Создадим таблицу и попробуем наполнить её последовательностью с помощью sequence dbo.seq02.

--Тестовая таблица
create table dbo.TestTable01 ( i tinyint, val varchar(20) )
go
--Вставка одной записи
insert into dbo.TestTable01
values ( next value for dbo.seq02, 'Val01' )
go
--Вставка нескольких записей
insert into dbo.TestTable01
values ( next value for dbo.seq02, 'Val02' )
     , ( next value for dbo.seq02, 'Val03' )
     , ( next value for dbo.seq02, 'Val04' )
go
--Выборка из таблицы
select * from dbo.TestTable01

Теперь посмотрим, какие параметры имеет наша последовательность dbo.seq02:

select start_value
     , increment
     , minimum_value
     , maximum_value
     , current_value
  from sys.sequences
  where object_id = object_id ( 'dbo.seq02', 'SO' )

Теперь попробуем создать новую последовательность, задав при этом явно стартовое значение, инкремент, минимальное и максимальное значение:

create sequence dbo.seq03 as tinyint
  start with 10  --Начинаем с 10
  increment by 5 --Приращение = 5
  minvalue 5     --Минимум последовательности
  maxvalue 100   --Максимум последовательности
go  
--Посмотрим параметры нашей новой последовательности:
select start_value
     , increment
     , minimum_value
     , maximum_value
     , current_value
  from sys.sequences
  where object_id = object_id ( 'dbo.seq03', 'SO' )

Заполним новую таблицу несколькими значениями последовательности dbo.seq03:

--Тестовая таблица
create table dbo.TestTable02 ( i tinyint, val varchar(20) )
go
--Вставка нескольких записей
insert into dbo.TestTable02
values ( next value for dbo.seq03, 'Val01' )
     , ( next value for dbo.seq03, 'Val02' )
     , ( next value for dbo.seq03, 'Val03' )
go
--Выборка из таблицы
select * from dbo.TestTable02

В следующем примере я создам новую таблицу и присвою одному из её полей в качестве значения по умолчанию последовательность dbo.seq03 (некий аналог поля со свойством IDENTITY)

--Тестовая таблица
create table dbo.TestTable03 ( i tinyint default next value for dbo.seq03, val varchar(20) )
go
--Вставка нескольких записей
insert into dbo.TestTable03 ( val )
values ( 'Val01' )
     , ( 'Val02' )
     , ( 'Val03' )
go
--Выборка из таблицы
select * from dbo.TestTable03

Мы описали все основные параметры последовальностей, но остались ещё два ключевых параметра - это параметр [ CYCLE | NO CYCLE ] и [ CACHE [ ] | NO CACHE ].

[ CYCLE | NO CYCLE ] - параметр очень важный, т.к. именно он позволяет задать цикличность нашей последовательности, т.к. по умолчанию значение NO CYCLE и при достижении максимального значения наша последовательность не сможет сгенерировать новое значение, в результате ошибка:
Msg 11728, Level 16, State 1, Line 1 The sequence object 'seq03' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Для "перезапуска" нашей последовательности необходимо выполнить скрипт:

alter sequence dbo.seq03 restart with 10 

Именно для того, чтобы не делать "перезапуск" последовательности руками, мы используем параметр CYCLE. Учтите, что циклическое повторение начинается не с начального, а с минимального или максимального (если последовательность отрицательная) значения.

create sequence dbo.seq04 as tinyint
  start with 20  --Начинаем с 20
  increment by 10 --Приращение = 5
  minvalue 0     --Минимум последовательности
  maxvalue 50   --Максимум последовательности
  cycle
go  
--Тестовая таблица
create table dbo.TestTable04 ( i tinyint, val varchar(20) )
go
--Вставка записей
insert into dbo.TestTable04
values ( next value for dbo.seq04, 'Val01' )
     , ( next value for dbo.seq04, 'Val02' )
     , ( next value for dbo.seq04, 'Val03' )
     , ( next value for dbo.seq04, 'Val04' )
     , ( next value for dbo.seq04, 'Val05' )
     , ( next value for dbo.seq04, 'Val06' )
     , ( next value for dbo.seq04, 'Val07' )
     , ( next value for dbo.seq04, 'Val08' )
     , ( next value for dbo.seq04, 'Val09' )
     , ( next value for dbo.seq04, 'Val10' )
go
select * from dbo.TestTable04

Последний параметр - это [ CACHE [ ] | NO CACHE ]

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

Например, если выбран размер кэша 50, то SQL Server не кэширует 50 отдельных значений. Кэшируется только текущее значение и число значений, оставшихся в кэше. Это значит, что объем памяти для хранения кэша всегда равен размеру двух экземпляров типа данных объекта последовательности.

Если параметр кэширования задан без указания размера кэша, то размер выбирается компонентом Database Engine. Однако пользователям не следует полагаться на предсказуемость выбора. Microsoft может изменить этот метод вычисления размера кэша без предварительного уведомления.

Если создание проводилось с параметром CACHE, то непредвиденное завершение работы (например, сбой электропитания) может привести к потере порядковых номеров, оставшихся в кэше.

Говоря о последовательностях, нельзя не упомянуть о функции sp_sequence_get_range, которая позволяет получить несколько значений из последовательности за один раз.
Синтаксис:

sp_sequence_get_range [ @sequence_name = ] N'' 
     , [ @range_size = ] range_size
     , [ @range_first_value = ] range_first_value OUTPUT 
    [, [ @range_last_value = ] range_last_value OUTPUT ]
    [, [ @range_cycle_count = ] range_cycle_count OUTPUT ]
    [, [ @sequence_increment = ] sequence_increment OUTPUT ]
    [, [ @sequence_min_value = ] sequence_min_value OUTPUT ]
    [, [ @sequence_max_value = ] sequence_max_value OUTPUT ]
    [ ; ]

Аргументы:

• [ @sequence_name = ] N'sequence'
  Имя объекта последовательности.Схема является необязательной.Аргумент sequence_name имеет тип nvarchar(776).
• [ @range_size = ] range_size
  Количество получаемых из последовательности значений. @range_size — bigint.
• [ @range_first_value = ] range_first_value
  Необязательный выходной параметр возвращает первое (минимальное или максимальное) значение объекта последовательности, используемое для вычисления запрошенного диапазона. @range_first_value — sql_variant с тем же базовым типом, что и у объекта последовательности, примененного в запросе.
• [ @range_last_value = ] range_last_value
  Необязательный выходной параметр возвращает последнее значение запрашиваемого диапазона. @range_last_value — sql_variant с тем же базовым типом, что и у объекта последовательности, примененного в запросе.
• [ @range_cycle_count = ] range_cycle_count
  Необязательный выходной параметр возвращает количество циклов объекта последовательности, которое потребовалось для возврата запрошенного диапазона. @range_cycle_count — int.
• [ @sequence_increment = ] sequence_increment
  Необязательный выходной параметр возвращает приращение объекта последовательности, которое использовалось для вычисления запрошенного диапазона. @sequence_increment — sql_variant с тем же базовым типом, что и у объекта последовательности в запросе.
• [ @sequence_min_value = ] sequence_min_value
  Необязательный выходной параметр возвращает минимальное значение объекта последовательности. @sequence_min_value — sql_variant с тем же базовым типом, что и у объекта последовательности в запросе.
• [ @sequence_max_value = ] sequence_max_value
  Необязательный выходной параметр возвращает максимальное значение объекта последовательности. @sequence_max_value — sql_variant с тем же базовым типом, что и у объекта последовательности в запросе.

Пример:

if object_id ( 'dbo.seq05', 'SO' ) is not null
drop sequence dbo.seq05
go
--Создадим новую последовательность
create sequence dbo.seq05 as tinyint
  start with 1   --Начинаем с 20
  increment by 1 --Приращение = 5
  minvalue 1     --Минимум последовательности
  maxvalue 200    --Максимум последовательности
  cycle
go  
--Посмотрим начальные параметры на нашей новой последовательности:
select start_value
     , increment
     , minimum_value
     , maximum_value
     , current_value
  from sys.sequences
  where object_id = object_id ( 'dbo.seq05', 'SO' )
--объявляем необходимые параметры для процедуры sp_sequence_get_range
declare @sequence_name nvarchar(100) = N'dbo.seq05'
      , @range_size int = 100
      , @range_first_value sql_variant
      , @range_last_value sql_variant
      , @sequence_increment sql_variant
      , @sequence_min_value sql_variant
      , @sequence_max_value sql_variant
-- запускаем процедуру sp_sequence_get_range
exec sp_sequence_get_range @sequence_name = @sequence_name
                        ,  @range_size = @range_size
                        ,  @range_first_value = @range_first_value output
                        ,  @range_last_value = @range_last_value output
                        ,  @sequence_increment = @sequence_increment output
                        ,  @sequence_min_value = @sequence_min_value output
                        ,  @sequence_max_value = @sequence_max_value output
-- показываем значения
select @range_size as [range_size]
     , @range_first_value as [range_first_value]
     , @range_last_value as [range_last_value]
     , @sequence_increment as [sequence_increment]
     , @sequence_min_value as [sequence_min_value]
     , @sequence_max_value as [sequence_max_value]
--Посмотрим текущие параметры на нашей последовательности:
select start_value
     , increment
     , minimum_value
     , maximum_value
     , current_value
  from sys.sequences
  where object_id = object_id ( 'dbo.seq05', 'SO' )

Ещё одна особенность последовательностей - это их работя в связке с предложением OVER

if object_id ( 'dbo.seq06', 'SO' ) is not null
drop sequence dbo.seq06
go
create sequence dbo.seq06 as tinyint
go  
if object_id ( 'dbo.TestTable05', 'U' ) is not null
drop table dbo.TestTable05
go
--Тестовая таблица
create table dbo.TestTable05 ( i tinyint, val varchar(20) )
go
--Вставка записей
insert into dbo.TestTable05
values ( next value for dbo.seq06, 'Val01' )
     , ( next value for dbo.seq06, 'Val02' )
     , ( next value for dbo.seq06, 'Val03' )
     , ( next value for dbo.seq06, 'Val04' )
     , ( next value for dbo.seq06, 'Val05' )
     , ( next value for dbo.seq06, 'Val06' )
     , ( next value for dbo.seq06, 'Val07' )
     , ( next value for dbo.seq06, 'Val08' )
     , ( next value for dbo.seq06, 'Val09' )
     , ( next value for dbo.seq06, 'Val10' )
go
--Значения у нашей последовательности
select start_value
     , increment
     , minimum_value
     , maximum_value
     , current_value
  from sys.sequences
  where object_id = object_id ( 'dbo.seq06', 'SO' )

--Использование последовательности в предложении OVER(!)
select *, next value for dbo.seq06 over (order by val desc) as [Order] 
from dbo.TestTable05
order by i

--Смотрим, как изменились значения у нашей последовательности
select start_value
     , increment
     , minimum_value
     , maximum_value
     , current_value
  from sys.sequences
  where object_id = object_id ( 'dbo.seq06', 'SO' )

Транзакции и Sequence

Так же, как и identity последовательности не "откатываю" свои значения, если транзакция была откачена, т.е. создаются "дырки".

if object_id ( 'dbo.seq07', 'SO' ) is not null
drop sequence dbo.seq07
go
create sequence dbo.seq07 as tinyint
go  
if object_id ( 'dbo.TestTable07', 'U' ) is not null
drop table dbo.TestTable07
go
--Тестовая таблица
create table dbo.TestTable07 ( i tinyint, val varchar(20) )
go

--Вставка записей в транзакции
begin tran
insert into dbo.TestTable07
values ( next value for dbo.seq07, 'Val01' )
     , ( next value for dbo.seq07, 'Val02' )
     , ( next value for dbo.seq07, 'Val03' )
go
rollback tran

--Значения у нашей последовательности
select start_value
     , increment
     , minimum_value
     , maximum_value
     , current_value
  from sys.sequences
  where object_id = object_id ( 'dbo.seq07', 'SO' )

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

• Identity - часть таблицв, а Sequence – объект БД
• В отличии от Identity у Sequence можно определять границы значений
• С помощью sp_sequence_get_range можно получить последовательность, что невозможно с Identity
• Цикличность значений можно задать так же только для Sequence

Кроме того, в производительности Sequence значительно выигрывает у Identity



Более подробно об этом тесте можно прочитать в блоге Aaron Bertrand - http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx

Ограничения использования Next Value для функций
Sequence ни в каких случаях нельзя использовать в сочетании с:

• Проверкой ограничений (constraints)
• Значениями по умолчанию
• Вычисляемыми колонками
• Представлениями (views)
• Пользовательскими функциями
• Пользовательскими функциями агрегации
• Подзапросами
• СТЕ (Common Table Expression)
• Подтаблицами
• Выражением TOP
• Выражением Over
• Выражением Output
• Выражением On
• Выражением Where
• Выражением Group By
• Выражением Having
• Выражением Order By
• Выражением Compute
• Выражением Compute By

Ссылки по теме:

• http://www.sql-server-performance.com/2011/sequence-sql-server-2011/
• http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx
• http://habrahabr.ru/blogs/sql/123446/
• http://www.gotdotnet.ru/blogs/decolores/1933/

Ещё одна приятная фишка, которая появится в новой версии SQL Server ( aka Denali ) - это параметр WITH RESULT SETS инструкции EXECUTE. Этот параметр позволяет не только делать выборку из процедуры, но и изменять результирующий рекордсет.

На форуме SQL.RU достаточно часто возникает вопрос, как сделать "Select из результата выполнения хранимой процедуры". В местном FAQ есть даже отделная ссылка с вариантами решения этой задачи - http://www.sql.ru/faq/faq_topic.aspx?fid=416.

Прежде, чем показать, как на лету менять рекордсет из процедуры, а именно типы полей и их имена в SQL Server 2011, вспомним, как решить эту задачу в текущих версиях SQL Server.
Для начала создадим тестовую таблицу из которой и будем делать выборку в теле процедуры:

if object_id ( 'dbo.Table01', 'U' ) is not null
drop table dbo.Table01
go
create table dbo.Table01 ( id int identity primary key
                         , val varchar(50)
                         , dt datetime
                         )
go
insert into dbo.Table01
select 'Val01', '20110801'
union all
select 'Val02', '20110802'
union all
select 'Val03', '20110803'
union all
select 'Val04', '20110804'
union all
select 'Val05', '20110805'
go

select * from dbo.Table01

А теперь создадим процедуру, которая делает выборку из этой таблицы:

if object_id ( 'dbo.Proc01', 'P' ) is not null
drop procedure dbo.Proc01
go
--Процедура, которая возвращает рекордсет из нашей таблицы
create procedure dbo.Proc01
as
set nocount on
select * from dbo.Table01
go
--Вызов процедуры
exec dbo.Proc01

А теперь предположим, что нам нужно использовать результат этой процедуры, при этом нам нужно изменить название поля val на MyVal. Т.к. в текущих версиях мы это сделать напрямую не можем, то есть несколько обходных решений:

1) Одно из решений - это обернуть наш запрос в виде табличной функции

if object_id ( 'dbo.func01', 'TF' ) is not null
drop function dbo.func01
go
--Табличная функция
create function dbo.func01()
returns @tbl table (  id int
                    , MyVal varchar(50)
                    , dt datetime
                   )
as
begin
insert into @tbl
select * from dbo.Table01
return
end
go
--Выборка из нашей функции
select * from dbo.func01()

2) Ещё один вариант - это вывод результат процедуры во временную таблицу и уже после делать выборку уже из этой временной таблицы:

if object_id ( 'tempdb..#tmp_table', 'U' ) is not null
drop table #tmp_table
go
--Временная таблица, для загрузки рекордсета из нашей процедуры
create table #tmp_table ( id int 
                        , MyVal varchar(50)
                        , dt datetime
                        )
go
--Вставляем записи
insert into #tmp_table
exec dbo.Proc01
go
--Последующая выборка из временной таблицы
select * from #tmp_table

3) Третий способ - это использование функций OPENROWSET либо OPENQUERY

--Создадим линкед сервер, который ссылается на этот же сервер БД
exec master..sp_addlinkedserver N'(local)'
                              , N'SQL Server'
go
--Включаем настройку 'Ad Hoc Distributed Queries', т.к. иначе получим ошибку:
---------------------------------------
--Msg 15281, Level 16, State 1, Line 2
--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' 
--because this component is turned off as part of the security configuration for this server. 
--A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. 
--For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
--------------------------------------- 
exec sp_configure 'show advanced options', 1
reconfigure
go
exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go
--Выборка из процедуры, через OPENROWSET
select id, val as MyVal, dt from openrowset ( 'SQLNCLI'
                                            , 'Server=(local);Trusted_Connection=yes;'
                                            , 'exec test.dbo.Proc01'
                                            )
go
--Выборка из процедуры, через OPENQUERY
select id, val as MyVal, dt from openquery ( [(local)],  'exec test.dbo.Proc01' )

go

Ещё несколько вариантов можно подглядеть у Erland Sommarskog, SQL Server MVP - http://www.sommarskog.se/share_data.html

А теперь пример с использованием "фишек" SQL Denali:

exec dbo.Proc01
with result sets
 (
   ( id int
   , MyVal varchar(50)
   , dt datetime   
   )
 );

Но, как я уже писал выше, мы можем не только на лету менять имена полей, но и их тип:

exec dbo.Proc01
with result sets
 (
   ( id int
   , MyVal varchar(50)
   , dt date   
   )
 );

Ссылки по теме:

• http://msdn.microsoft.com/ru-ru/library/ms188332(v=sql.110).aspx
• http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--execute-with-result-sets-136850
• http://www.sql.ru/faq/faq_topic.aspx?fid=416
• http://www.sommarskog.se/share_data.html