Что такое сиквенс в базе данных

Сиквенсы (Sequences)

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

Сиквенс это бесценный инструмент для генерации значений первичного ключа. Многие приложения нуждаются в автоматически сгенерированных значениях первичного ключа. Например номерпокупателя и номер заказа: бизнес-аналитики могут решить что каждый заказ должен иметь уникальный номер, которые последовательно увеличивается. В других приложениях вы можете не иметь явных бизнес требований к ключам, но они понядобятся для организации ссылочной целостности. Например в учёте телефонных звонков: с точки зрения бизнес идентификатором является телефонный номер (строка) и звонком будет значение телефона и время начала звонка. Эти типы данных очень сложные для использования их как первичных ключей для больших объёмов которые обязательно будут в системе учёта звонков. Для записи этой информации гораздо легче использовать простые численные столбцы для определения первичных и внешних ключей. Значения этих столбцов могут основываться на сиквенсах.

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

На рисунке 7-6 показано как две сессий выбирают значения из сиквенса SEQ1. Обратите внимание что каждый запрос SEQ1.NEXTVAL генерирует уникальный номер. Значение создаётся по порядку в зависимости от времени обращения, и значение увеличивается глобально а не для одной сессии.

Создание сиквенсов

Полный синтаксис для создания сиквенса

CREATE SEQUENCE [schema.]sequencename

[INCREMENT BY number]

[START WITH number]

[MAXVALUE number | NOMAXVALUE]

[MINVALUE number | NOMINVALUE]

[CACHE number | NOCACHE]

Создание сиквенса может быть очень простым. Например сиквенс использованный на рисунке 7-6 был создан с помощью команды

create sequence seq1;

Список доступных параметров

Директива CYCLE используется очень редко так как позволяет генерировать дубликаты. Если сиквенс используется для генерации значений первичного ключа, CYCLE имеет смысл только есть функция в БД которая удаляет старые записи быстрее чем сиквенс генерирует новые.

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

The default number of values to cache is only 20. Experience shows that this is usually not enough. If your application selects from the sequence 10 times a second, then set the cache value to 50 thousand. Don’t be shy about this

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

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

The CURRVAL of a sequence is the last value issued to the current session, not necessarily the last value issued. You cannot select the CURRVAL until after selecting the NEXTVAL.

Типичным примером использования сиквенса является генерация значений первичного ключа. Следующий пример использует сиквенс ORDER_SEQ для генерации уникальных значений номера заказа и сиквенс LINE_SEQ для генерации уникального значения строки заказа. Вначале создаётся сиквенс (один раз)

create sequence order_seq start with 10;

create sequence line_seq start with 10;

Затем вставка заказа и пунктов заказа в одной транзакции

insert into orders (order_id,order_date,customer_id)

insert into order_items (order_id,order_item_id,product_id)

insert into order_items (order_id,order_item_id,product_id)

Первая команда INSERT создает заказ с уникальным номером из сиквенса ORDER_SEQ для покупателя с номером 1000. Затем вторая и третья команды INSERT добавляют два элемента заказа используя текущее значение сиквенса ORDER_SEQ как значение для внешнего ключа соединяющего элементы заказа с заказом и следующее значение сиквенса LINE_SEQ для генерации уникального идентификатора каждого элемента. И наконец транзакция подтверждается.

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

COMMIT не обязателен для подвтерждения увеличения счетчика: увеличение счётчика происходи сразу и навсегда и становится видимым для всех в момент увеличения. Нельзя отменить увеличение счётчика. Сиквенс обновляется вне зависимости от механизма управления транзакциями. Поэтому всегда будут пропавшие номера. Разрывы могут быть большими если БД перезапускается и CACHE директива использовалась для счётчика. Все номера которые были сгенерированы и не выбирались будут потеряны в момент выключения базы данных. После следующего запуска текущее значение будет последнее сгенерированное, а не последнее использованное. Таким образом для значения по умолчанию 20, каждый перезапуск приводит к потере 20 номеров.

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

create table current_on(order_number number);

insert into current_on values(10);

Тогда код для создания заказа станет следующим

update current_on set order_number=order_number + 1;

insert into orders (order_number,order_date,customer_number)

values ((select order_number from current_on),sysdate,’1000′);

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

После создания сиквенса он может быть изменена. Синтаксис команды следующий

ALTER SEQUENCE sequencename

[INCREMENT BY number]

[START WITH number]

[MAXVALUE number | NOMAXVALUE]

[MINVALUE number | NOMINVALUE]

[CACHE number | NOCACHE]

Команда ALTER такая же как команда CREATE за одним исключением: нельзя установить начальное значение. Если вы хотите обновить начальное значение – то единственный способ это удалить сиквенс и создать новый. Для изменения значения CACHE для увеличения производительности можно выполнить следующую команду

MS SQL 2011 – новый объект Sequence

Возможность, которой не удивишь нынче пользователей Oracle, DB2, PostgreSQL и множества других реляционных баз данных, наконец-то появилась и в MS SQL Server. На арене Sequence!

Sequence – генерирует последовательность чисел так же как и identity. Однако основным плюсом sequence является то, что последовательность не зависит от какой-либо конкретной таблицы и является объектом базы данных.

Рассмотрим пример скрипта написанного на SQL Server 2008. Создание простой таблицы с двумя колонками, одна из которых будет автоинкрементной.

Похожим образом создадим еще одну таблицу.

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

Общий синтаксис для команды выглядит так:

Создадим последовательность чисел:

После выполнения указанного скрипта, в браузере объектов базы, в узле Sequences можно найти наш объект.

После того как объект создан, можно его использовать в создании и заполнении таблиц как показано ниже:

Если создать вторую таблицу в таком же духе, то можно снова использовать GenerateNumberSequence и получать сквозную нумерацию объектов.

Последовательность (Sequence) которую мы создали, можно посмотреть в системном каталоге sys.sequences.

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

• Int
• Smallint
• Tinyint
• Bigint
• Decimal
• Numeric

Проверим на практике, что скажет SQL Server при задании начального числа вне допустимого диапазона. Начнем с левой границы.

An invalid value was specified for argument ‘START WITH’ for the given data type.

Что и ожидалось. Теперь нарушим правую границу.

Сервер сообщит нам об ошибке так:

The sequence object ‘GenerateNumberSequence’ cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.

И если мы обратим внимание на колонку Is_Exhausted в каталоге sys.sequences, то увидим, что значение стало равно 1. Что говорит нам о невозможности дальнейшего использования данной последовательности.

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

The sequence object ‘GenerateNumberSequence’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Это можно трактовать как просьбу движка рестартовать указанную последовательность. Для этого необходимо воспользоваться конструкцией RESTART WITH.

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

А потом выполнить скрипт:

То результат будет таким:

Последовательность началась с заданного значения.

Получить минимальные и максимальные значения можно из каталога sys.sequences.

MIN и MAX значения

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

Минимальное значение равняется 10, максимальное – 20, но мы пытаемся задать начальное значение равное единице. Это за пределами допустимого диапазона и поэтому нас порадуют сообщением:

The start value for sequence object ‘GenerateNumberSequence’ must be between the minimum and maximum value of the sequence object.

Далее можем представить, что следующее значение в последовательности нарушает границу. В таком случае получим ошибку:

The sequence object ‘GenerateNumberSequence’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

• Использовать служебные слова Restart или Restart With.
• Использовать опцию CYCLE

Опция CYCLE

Данная опция зацикливает последовательность и, достигнув максимального значения, последовательность продолжается с минимального. Например:

После того как максимальное значение было достигнуто, результаты станут такими:

Для выборки использовался запрос:

Если внимательно посмотреть на вывод, то можно заметить, что записи были перепутаны. Если бы мы не использовали последовательности, то вывод был бы

Но из-за того, что вторая запись пересекла диапазон допустим значений, номер был сброшен на минимальное значение, заданное для последовательности (10). Если сейчас посмотреть в каталог sys.sequences, то будет видно, что текущее значение равняется 10.

В следующий раз, заполнение таблицы могло бы быть таким:

В этот момент Sequence проверит порядок в котором записи будут вставлены и так как “Violet” идет раньше “Tape” и текущий номер равен 10, записи будут вставлены как:

Следующее_значение =Текущее_значение +Сдвиг т.е. 10 +1 будет присвоено для “Violet”. Теперь значение Sequence = 11 и для второй записи значение будет 12 следуя то же самой формуле.

Опция NO CYCLE

Sequence в сочетании с Over()

Можно использовать последовательность вместе с выражением Over для генерирования порядковых номеров как показано ниже:

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

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

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

Функция sp_sequence_get_range

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

Вот что будет в результате выполнения:

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

SEQUENCE IN SQL SERVER

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH start_value]
[ INCREMENT BY increment_value ]
[ < MINVALUE [ min_value ] >| < NO MINVALUE >]
[ < MAXVALUE [ max_value ]>| < NO MAXVALUE >]
[ CYCLE | < NO CYCLE >]
[ < CACHE [ cache_size ] >| < NO CACHE >]
[; ]

• The sequence name specifies a name to uniquely identify.
• Start with states the first value of sequence numbers.
• Increment by specifying the interval between sequence numbers.
• Min value states the minimum value of the sequence.
• Max value states the maximum value produced by the sequence.
• The cycle indicates the sequence is continued for generates the value starts from the min_value or max_value.
• Cache specifies the values to improve the performance.

EXAMPLE OF SEQUENCE

• In the following example, test_seq is a sequence name that incremented by 1, starts at 112, the max value is 120, and min value 112, does not cycle, and caches 7.

CREATE SEQUENCE test_seq INCREMENT BY 1

START WITH 112 MAXVALUE 120 MINVALUE 112

RESULT

• We can access the value help of CURVAL and NEXTVAL.
• The NEXTVAL returns the original value of the sequence.
• The CURVAL returns the current value which is the next value to the NEXTVAL.
• If the sequence is not yet used, the values of NEXTVAL and CURVAL are the same.

SELECT test_seq.NEXTVAL FROM dual

SELECT test_seq.CURVAL FROM dual

• In the below example, the NEXTVAL function generates a sequence number from the dual sequence object.
• When we execute the following statement again, then the value incremented by 1.

SELECT test_seq.NEXTVAL FROM dual

• Syntax:- ALTER SEQUENCE <Seq_Name> variable value
• Example:- ALTER SEQUENCE test_seq MAXVALUE 125

• Syntax:- DROP SEQUENCE <Seq_Name>
• Example:- DROP SEQUENCE test_seq

If you are new to database learning — SQL Server recommended is the following must-watch the video: —

Что такое сиквенс в базе данных

Use the CREATE SEQUENCE statement to create a sequence , which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

Pseudocolumns for more information on using the CURRVAL and NEXTVAL

«How to Use Sequence Values» for information on using sequences

ALTER SEQUENCE or DROP SEQUENCE for information on modifying or dropping a sequence

To create a sequence in your own schema, you must have the CREATE SEQUENCE system privilege.

To create a sequence in another user’s schema, you must have the CREATE ANY SEQUENCE system privilege.