] | 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
Ссылки по теме:
Подписаться