Последовательности (SEQUENCE) в SQL Server Denali

Последовательности (SEQUENCE) - одно из нововведений в Denali, которое предоставляет последовательность числовых значений в соответствии с параметрами, которые мы задали при создании последовательности. Создать последовательность можно так:

1. create sequence test_sequence
2. as int
3. start with 1
4. increment by 1

* This source code was highlighted with Source Code Highlighter.

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

Рассмотрим основные параметры, которые можно задать при создании последовательности. Как видно из примера создания последовательности, приведенного выше, можно указать тип последовательности - любой целочисленный тип (а также decimal и numeric с масштабом 0), по умолчанию используется bigint. Также можно задать такие параметры: стартовое значение последовательности с помощью конструкции srart with; значение на которое будет увеличиваться наше последующее значение - increment by; минимальное значение - minvalue; максимальное значение - maxvalue; цикличность cycle (по умолчанию отключено) - перезапуск последовательности с минимального значения определенного с помощью параметра minvalue (либо с минимального значения типа данных если не явно не указан параметр minvalue) при достижении максимального значения, указанного с помощью параметра maxvalue (или максимального значения типа данных если явно не указан параметр maxvalue); кеширование  элементов последовательности - cache.

Получить значение последовательности можно с помощью конструкции next value for , например:

1. select next value for test_sequence

* This source code was highlighted with Source Code Highlighter.

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

1. --последовательность c отключенным кешом
2. create sequence test_sequence
3. as int
4. start with 1
5. increment by 1
6. no cache
7. --последовательность c включенным кешом
8. create sequence test_sequence_cache
9. as int
10. start with 1
11. increment by 1
12. cache 1000
13. --таблицы
14. create table [dbo].[test_table_identity]([Id] [int] identity(1,1) not null primary key)
15. create table [dbo].[test_table_sequence]([Id] [int] not null primary key)
16. create table [dbo].[test_table_sequence_cache]([Id] [int] not null primary key)
17. --таблица для записи результатов
18. create table #time
19. (
20.     step nvarchar(100),
21.     [time] datetime not null default sysdatetime()
22. )
23. --блок вставки данных
24. insert into #time (step,[time]) values ('start_sequence_cache',default)
25. go
26. INSERT INTO [dbo].[test_table_sequence_cache] ([Id]) VALUES (next value for test_sequence_cache)
27. go 100000
28. insert into #time (step,[time]) values ('end_sequence_cache',default)
29. go
30.  
31. insert into #time (step,[time]) values ('start_sequence',default)
32. go
33. INSERT INTO [dbo].[test_table_sequence] ([Id]) VALUES (next value for test_sequence)
34. go 100000
35. insert into #time (step,[time]) values ('end_sequence',default)
36. go
37.  
38. insert into #time (step,[time]) values ('start_identity',default)
39. go
40. INSERT INTO [dbo].[test_table_identity] default VALUES
41. go 100000
42. insert into #time (step,[time]) values ('end_identity',default)
43. go

* This source code was highlighted with Source Code Highlighter.

Если просмотреть данные из временной таблицы #time о времени, которое было потрачено для заполнения таблиц разными способами, то увидим следующие результаты (миллисекунды):

Как видно время вставки данных (100 тыс. строк) практически одинаковое. С небольшим отрывом лидирует вставка на основе последовательности с включенным кешом, потом идет вставка на основе identity и на последнем месте вставка на основе последовательности с отключенным кешом.

Теперь посмотрим как будет вести себя sequence на месте row_number(). В качестве подопытной таблицы у нас будет одня из ранее созданных и заполненных. Также рассмотрим вариант с включенным и отключенным кешом для последовательности. Собственно код:

1. dbcc freeproccache
2. set statistics time on
3. go
4. select ROW_NUMBER() over(order by id) row_num, id
5. from for_tests.dbo.test_table_identity
6. go
7. select next value for test_sequence over(order by id) seq_num, id
8. from for_tests.dbo.test_table_identity
9. go
10. select next value for test_sequence_cache over(order by id) seq_num, id
11. from for_tests.dbo.test_table_identity
12. go
13. set statistics time off

* This source code was highlighted with Source Code Highlighter.

Результаты:
- row_number

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 720 ms.

- secuence (кеш отключен)

SQL Server Execution Times:
   CPU time = 640 ms,  elapsed time = 801 ms.

-secuence (кеш включен)

 SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 654 ms.

Тут явно лидирует последовательность с включенным кешом. Самый худший показатель при отключенном кеше у последовательности.
Если во время выполнения предыдущего запроса включить Client Statistics то можно увидеть еще одну очень интересную деталь:

Зеленым цветом выделено количество данных полученных с сервера при использовании последовательности (с включенным и отключенным кешом), а красным цветом - при использовании row_number. Еще один плюс в пользу использования последовательности.
В статье Аарона Бертранда можно прочитать еще о приросте производительности sequence над identity.

И еще немного полезной информации о sequence.
Посмотреть информацию о последовательности можно с помощью системной вьюхи sys.sequences :

1. select * from sys.sequences where name = 'test_sequence';

* This source code was highlighted with Source Code Highlighter.

Также в нашем распоряжении имеется функция sp_sequence_ger_range - позволяющая получить несколько значений из последовательности за одни раз.
Можно рестартануть последовательность:

1. alter sequence test_sequence
2. restart with 1

* This source code was highlighted with Source Code Highlighter.

Выводы.
Плюсы:
- отдельный объект в бд, не привязан к таблицам, можно задавать права доступа;
- прирост производительности;
- новые возможности - цикличность, границы и др.
Минусы:
- есть вероятность пропуска/потери номеров в последовательности.

Полезные ссылки:
- Описание в MSDN - http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx