Сиквенсы (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 для увеличения производительности можно выполнить следующую команду
alter sequence order_seq cache 1000;
Для удаления сиквенса выполните команду
drop sequence order_seq;
- DDL и объекты схемы — Итоги
- Создание простой таблицы
- Ограничения
- Объекты БД
- Индексы
CREATE SEQUENCE
Последовательность CREATE SEQUENCE – это объект базы данных, который генерирует целые числа в соответствии с правилами, установленными во время его создания. Для последовательности можно указывать как положительные, так и отрицательные целые числа. В системах баз данных последовательности применяют для самых разных целей, но в основном для автоматической генерации первичных ключей. Тем не менее к первичному ключу таблицы последовательность никак не привязана, так что в некотором смысле она является еще и объектом коллективного пользования. Если первичный ключ нужен лишь для обеспечения уникальности, а не для того, чтобы нести определенный смысл, последовательность является отличным средством. Последовательность создается командой CREATE SEQUENCE.
CREATE SEQUENCE
Синтаксис команды CREATE SEQUENCE
Основные ключевые слова и параметры CREATE SEQUENCE:
- schema —схема, в которой создается последовательность. Если schema опущена, Oracle создает последовательность в схеме пользователя.
- sequence — имя создаваемой последовательности
- startwith— позволяет создателю последовательности указать первое генерируемое ею значение. После создания последовательность генерирует указанное в start with значение при первой ссылке на ее виртуальный столбец NEXTVAL
- increment by n — определяет приращение последовательности при каждой ссылке на виртуальный столбец NEXVAL. Если значение не указано явно, по умолчанию устанавливается 1. Для возрастающих последовательностей устанавливается положительное n, для убывающих, или последовательностей с обратным отсчетом — отрицательное
- minvalue — определяет минимальное значение, создаваемое последовательностью. Если оно не указано, Oracle применяет значение по умолчанию NOMINVALUE
- nominvalue — указывает, что минимальное значение равно 1, если последовательность возрастает, или -10 26 , если последовательность убывает
- maxvalue — определяет максимальное значение, создаваемое последовательностью. Если оно не указано, Oracle применяет значение по умолчанию NOMAXVALUE
- nomaxvalue — указывает, что максимальное значение равно 10 27 , если последовательность возрастает, или -1, если последовательность убывает. По умолчанию принимается NOMAXVALUE
- cycle — позволяет последовательности повторно использовать созданные значения при достижении MAXVALUE или MINVALUE. Т.е. последовательность будет продолжать генерировать значения после достижения своего максимума или минимума. Возрастающая последовательность после достижения своего максимума генерирует свой минимум. Убывающая последовательность после достижения своего минимума генерирует свой максимум. Если циклический режим нежелателен или не установлен явным образом, Oracle применяет значение по умолчанию – NOCYCLE. Указывать CYCLE вместе с NOMAXVALUE или NOMINVALUE нельзя. Если нужна циклическая последовательность, необходимо указать MAXVALUE для возрастающей последовательности или MINVALUE – для убывающей
- nocycle — указывает, что последовательность не может продолжать генерировать значения после достижения своего максимума или минимума
- cachen — указывает, сколько значений последовательности ORACLE распределяет заранее и поддерживает в памяти для быстрого доступа. Минимальное значение этого параметра равно 2. Для циклических последовательностей это значение должно быть меньше, чем количество значений в цикле. Если кэширование нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию – 20 значений.
- order — гарантирует, что номера последовательности генерируются в порядке запросов. Эта опция может использоваться, к примеру, когда номера последовательности предстают в качестве отметок времени. Гарантирование порядка обычно не существенно для тех последовательностей, которые используются для генерации первичных ключей. Если упорядочение нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию NOORDER
- noorder — не гарантирует, что номера последовательности генерируются в порядке запросов
Пример 1 CREATE SEQUENCE Создание последовательности sequence_1.s Первое обращение к этой последовательности возвратит 1. Второе обращение возвратит 11. Каждое следующее обращение возвратит значение, на 10 большее предыдущего:
Пример 2 CREATE SEQUENCE Создание последовательности sequence_2. Последовательность убывающая, циклическая, при достижении нуля последовательность вновь обращается к старшему числу. Такой последовательностью удобно пользоваться в тех программах, где до наступления некоторого события должен быть выполнен обратный отсчет:
CREATE SEQUENCE sequence_2 START WITH 20 INCREMENT BY –1 MAXVALUE 20 MINVALUE 0 CYCLE ORDER CACHE 2;
После создания последовательности к ней можно обращаться через псевдостолбцы CURRVAL (возвращает текущее значение последовательности) и NEXTVAL (выполняет приращение последовательности и возвращает ее следующее значение). Текущее и следующее значения последовательности пользователи базы данных получают, выполняя команду SELECT. Последовательности – не таблицы, а простые объекты, генерирующие целые числа с помощью виртуальных столбцов, поэтому нужна общедоступная таблица словаря данных DUAL, из которой будут извлекаться данные виртуальных столбцов.
Первое обращение к NEXTVAL возвращает начальное значение последовательности. Последующие обращения к NEXTVAL изменяют значение последовательности на приращение, которое было определено, и возвращают новое значение. Любое обращение к CURRVAL всегда возвращает текущее значение последовательности, а именно, то значение, которое было возвращено последним обращением к NEXTVAL.
Прежде чем обращаться к CURRVAL в текущем сеансе работы, необходимо хотя бы один раз выполнить обращение к NEXTVAL. В одном предложении SQL приращение последовательности может быть выполнено только один раз. Если предложение содержит несколько обращений к NEXTVAL для одной и той же последовательности, то ORACLE наращивает последовательность один раз, и возвращает одно и то же значение для всех вхождений NEXTVAL. Если предложение содержит обращения как к CURRVAL, так и к NEXTVAL, то ORACLE наращивает последовательность и возвращает одно и то же значение как для CURRVAL, так и для NEXTVAL, независимо от того, в каком порядке они встречаются в предложении. К одной и той же последовательности могут обращаться одновременно несколько пользователей, без какого-либо ожидания или блокировки:
Чтобы обратиться к текущему или следующему значению последовательности, принадлежащей схеме другого пользователя, пользователь должен иметь либо объектную привилегию SELECT по этой последовательности, либо системную привилегию SELECT ANY SEQUENCE, и должен дополнительно квалифицировать эту последовательность именем содержащей ее схемы: имя схемы>.имя последовательности >.CURRVAL имя схемы>.имя последовательности >.NEXTVAL Значения CURRVAL и NEXTVAL используются в следующих местах:
- в списке SELECT предложения SELECT
- в фразе VALUES предложения INSERT
- в фразе SET предложения UPDATE.
Нельзя использовать значения CURRVAL и NEXTVAL в следующих местах:
- в подзапросе
- в предложении SELECT с оператором DISTINCT
- в предложении SELECT с фразой GROUP BY или ORDER BY
- в предложении SELECT, объединенном с другим предложением SELECT оператором множеств UNION
- в фразе WHERE предложения SELECT
- в умалчиваемом (DEFAULT) значении столбца в предложении CREATE TABLE или ALTER TABLE
- в условии ограничения CHECK.
SELECT SEQUENCE. Пример 3.Действие циклической последовательности sequence_2 при достижении ею значения MINVALUE:
SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 20 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 19 ….. SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 1 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 0 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 20
CREATE SEQUENCE. Пример 4. В следующем примере SEQUENCE после ссылки на столбец NEXVAL значение CURRVAL обновляется так, чтобы соответствовать значению NEXVAL, а предыдущее значение CURRVAL теряется:
SQL> SELECT sequence_2.CURRVAL FROM dual; CURRVAL————— 20 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 19 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 18 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 17 SQL> SELECT sequence_2.CURRVAL FROM dual; CURRVAL————— 17
CREATE SEQUENCE. Пример 5. Ссылка на последовательности при изменении данных:
INSERT INTO emp VALUES (empseq.nextval, ‘LEWIS’, ‘CLERK’, 7902, SYSDATE, 1200, NULL, 20); UPDATE emp SET deptno = empseq.currval WHERE ename = ‘Jones’
ALTER SEQUENCE. Пример 6. Любой параметр последовательности можно изменить командой ALTER SEQUENCE. Новое значение вступает в силу немедленно. Все параметры последовательности, не указанные в команде ALTER SEQUENCE, остаются без изменений:
ALTER SEQUENCE sequence_2 INCREMENT BY –4;
Когда последовательность больше не нужна, ее можно удалить. Для этого администратор базы данных или владелец последовательности должен выполнить команду DROP SEQUENCE. В результате виртуальные столбцы последовательности NEXVAL и CURRVAL — переводятся в разряд неиспользуемых. Но, если последовательность применялась для генерации значений первичных ключей, созданные ею значения останутся в базе данных. Каскадного удаления значений, сгенерированных последовательностью, при ее удалении не происходит. DROP SEQUENCE. Пример 7. Удаление последовательности SEQUENCE:
Используем sequence в MySQL
Хочу рассказать о том, как создать сквозной монотонно возрастающий целочисленный id в MySQL (это называется sequence в других БД). Зачем это нужно? Например, в системе есть два вида задач для сотрудников, у каждого вида задач есть свой набор атрибутов и свой жизненный цикл (набор статусов). Для того чтобы не городить огород в коде приложения для корректного чтения разных сущностей из БД и не плодить колонки с NULL лучше разнести сущности по разным таблицам.
Но при всем при этом хочется иметь возможность эти задачи уникально идентифицировать в рамках системы. Например, в сервисах «одно окно» в одном разделе приложения представлено много схожих, но разных по workflow сущностей, как в примере выше. Но проблема в том, что если сущности лежат в разных таблицах, то у каждой из них свой autoincrement, и следовательно, нумерация будет пересекаться.
Один из вариантов решения это использование идентификации с помощью id + type . Но это несколько усложняет архитектуру, ведь об этом придется помнить как на backend, так и на frontend. И возникает возможность случайно совершить действие с другой задачей, просто-напросто перепутав id.
Альтернативное решение это использовать uuid, но он имеет ряд недостатков:
- uuid плохо влияет на работу primary key и не очень хорошо дружит с Btree индексом
- uuid разумеется занимает гораздо больше места, чем integer
- И пожалуй самый главный недостаток это то, что с ним не очень удобно работать, особенно когда он присутствует в ссылках на ресурс. Отсутствует «человекочитаемость». Например, ff86e090-0625-11ea-9f44-8c16456798f1 невозможно сходу запомнить в отличие от числа 1256 . Особенно на это жалуются тестировщики и отдел сопровождения, когда пользователь приложил скриншот, на котором url c несколькими uuid, и им приходится аккуратно посимвольно переписывать их со скриншота.
Эмулировать sequence в MySQL довольно просто. Итак, начнем.
Подготовка
Для примера создадим две таблицы:
CREATE TABLE task_1 ( id INT UNSIGNED NOT NULL DEFAULT 0, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE task_2 ( id INT UNSIGNED NOT NULL DEFAULT 0, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) );Для поля id у нас нет AUTOINCREMENT, потому что мы будем брать значение из нашего будущего sequence, DEFAULT 0 — потому что поле с primary key не может быть null, а следовательно, нужно иметь какое-то значение по умолчанию. Создадим так же таблицу, где будем хранить наш сквозной id.
CREATE TABLE sequence ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL );#1 Через INSERT
Будем реализовывать автоматическую выдачу нового id через триггеры для каждой из таблиц.
CREATE TRIGGER task_1_before_insert BEFORE INSERT ON task_1 FOR EACH ROW BEGIN INSERT INTO sequence SET SET NEW.id = LAST_INSERT_ID(); END; CREATE TRIGGER task_2_before_insert BEFORE INSERT ON task_2 FOR EACH ROW BEGIN INSERT INTO sequence SET SET NEW.id = LAST_INSERT_ID(); END;Данные триггеры будут срабатывать при каждом INSERT в таблицы task_1 и task_2 . В начале, мы делаем вставку в таблицу sequence , а затем получаем последний id (LAST_INSERT_ID) и используем его для вставки в целевую таблицу (с помощью переменной NEW). Протестируем наше решение путем создания задач в каждой из таблиц.
INSERT INTO task_1 SET name = 'example 1'; INSERT INTO task_2 SET name = 'example 2';В итоге таблицы будут содержать следующие данные:
Объект SEQUENCE (последовательность) в Microsoft SQL Server
Всем привет! Сегодня мы поговорим про объект SEQUENCE (последовательность) в Microsoft SQL Server, Вы узнаете, что такое SEQUENCE, для чего этот объект нужен, какие у него особенности, и, конечно же, научитесь им пользоваться (создавать, изменять, удалять и использовать).
SEQUENCE в Microsoft SQL Server
SEQUENCE – это объект SQL Server, который генерирует числовые значения в определенной последовательности в соответствии с заданной спецификацией.
В числе основных предназначений SEQUENCE является формирования значений для столбца идентификаторов в таблицах.
В Microsoft SQL Server SEQUENCE, как объект, появился только в 2012 версии, ранее для формирования значений столбцов идентификаторов в таблицах использовалось свойство IDENTITY. Теперь для этих целей можно использовать и IDENTITY, и SEQUENCE, в чем их отличие я подробно расскажу в следующих материалах.
SEQUENCE – это пользовательский объект, т.е. последовательность создают пользователи точно так же, как и другие объекты: хранимые процедуры, функции и так далее. Для создания последовательности требуются соответствующие разрешения, например, CREATE SEQUENCE. А члены предопределенных ролей db_owner и db_ddladmin по умолчанию могут выполнять любые операции с последовательностями.
Последовательность числовых значений в Microsoft SQL Server может формироваться в возрастающем или убывающем порядке с заданным интервалом. Кроме того, можно настроить перезапуск (т.е. зацикливание) последовательности, когда она исчерпана, а также задать минимальное и максимальное значения последовательности.
Значение последовательности получают с помощью вызова функции NEXT VALUE FOR, которое возвращает одно значение, однако можно получить сразу несколько значений за один раз, это делается с помощью системной процедуры sp_sequence_get_range.
SEQUENCE можно изменить уже после создания, так же, как и другие объекты, это делается с помощью инструкции ALTER SEQUENCE.
Еще одной особенностью SEQUENCE является то, что в своей работе она использует кэширование, что повышает производительность для приложений, которые используют последовательность. Это достигается за счет того, что значения последовательности заранее создаются и хранятся в памяти, а при обращении к SEQUENCE за новым значением это значение возвращается из памяти, тем самым значительно уменьшается число операций дискового ввода-вывода, которые требуются для создания значений последовательности.
Управляется кэширование последовательности с помощью параметра CACHE, который по умолчанию включен. Для его отключения необходимо указать NO CACHE.
Посмотреть, какие последовательности есть в базе данных и получить все сопутствующие сведения о последовательностях (параметры, с которыми они создавались), Вы можете с помощью обращения к системному представлению sys.sequences.
Синтаксис SEQUENCE
CREATE SEQUENCE [schema_name].[sequence_name] [ AS [data type] ] [ START WITH < value >] [ INCREMENT BY < value >] [ MINVALUE [ < value >] | NO MINVALUE ] [ MAXVALUE [ < value >] | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE [ < value >] | NO CACHE ];
Описание синтаксиса
| Параметр | Описание |
| CREATE SEQUENCE | Инструкция создания объектов последовательности. |
| schema_name и sequence_name | Имя схемы и имя последовательности. |
| value | Значение параметра. |
| AS | Тип данных значения, которое будет возвращать последовательность. Допускаются только целочисленные значения: TINYINT, SMALLINT, INT, BIGINT или NUMERIC с масштабом 0. Более подробно о типах данных можете посмотреть в отдельном материале – Типы данных в T-SQL (Microsoft SQL Server). Если тип данных не указан, то по умолчанию используется BIGINT. |
| START WITH | Начальное значение, возвращаемое объектом последовательности. Это значение должно быть не больше максимального и не меньше минимального значения объекта последовательности. По умолчанию начальным значением у возрастающей последовательности является минимально возможное значение, а для убывающей последовательности – максимально возможное. |
| INCREMENT BY | Значение, на которое увеличивается (или уменьшается) значение объекта последовательности. Если данное значение отрицательное, то объект последовательности убывает, если положительное, то возрастает. По умолчанию используется значение 1. Данное значение не может быть равно 0. |
| MINVALUE | Минимальное значение объекта последовательности. По умолчанию минимальным значением для новой последовательности является минимальное значение для типа данных этой последовательности. Обращаю внимание, что для всех типов данных, кроме tinyint, минимальное значение – это отрицательное число. |
| MAXVALUE | Максимальное значение объекта последовательности. По умолчанию максимальным значением для последовательности является максимальное значение типа данных объекта последовательности. |
| CYCLE | Параметр показывает, должна ли последовательность быть перезапущена, как только она достигнет своего максимального или минимального значения. По умолчанию используется параметр NO CYCLE. |
| CACHE | Параметр, который управляет кэшированием значений объектов последовательности. По умолчанию имеет значение CACHE. Для отключения кэширования необходимо указать NO CACHE. |
Практически все параметры являются необязательными, для создания последовательности с параметрами по умолчанию необходимо указать всего лишь инструкцию создания последовательности и имя последовательности. Однако начальное значение последовательности лучше все-таки указывать, если она будет использоваться в качестве генератора идентификаторов для таблицы.
Примеры создания SEQUENCE в Microsoft SQL Server
Теперь давайте разберём, как создается последовательность в Microsoft SQL Server.
Для выполнения примеров у меня есть база данных Test, в которой я и буду создавать последовательность.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
Во всех примерах последовательность будет создаваться с названием TestSequence в схеме по умолчанию (dbo), поэтому в инструкциях я не буду указывать конкретное название схемы.
Создание SEQUENCE со значениями по умолчанию
CREATE SEQUENCE TestSequence;
Создание SEQUENCE с начальным значением 1 и увеличением на 1
CREATE SEQUENCE TestSequence START WITH 1 INCREMENT BY 1;
Создание SEQUENCE с начальным значением 10 и увеличением на 5
CREATE SEQUENCE TestSequence START WITH 10 INCREMENT BY 5;
Создание SEQUENCE с начальным значением 0 и уменьшением на 1
CREATE SEQUENCE TestSequence START WITH 0 INCREMENT BY -1;
Создание SEQUENCE с заданного числа и увеличением на 1
CREATE SEQUENCE TestSequence START WITH 12345 INCREMENT BY 1;
Создание SEQUENCE с заданным типом данных
CREATE SEQUENCE TestSequence AS TINYINT START WITH 1 INCREMENT BY 1;
Создание SEQUENCE с указанием максимального значения
CREATE SEQUENCE TestSequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;
Создание SEQUENCE с поддержкой цикла
CREATE SEQUENCE TestSequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50 CYCLE;
Примеры изменения SEQUENCE
Параметры существующего объекта SEQUENCE можно изменить. Это делается с помощью инструкции ALTER.
Изменение параметров существующей последовательности
В случае если Вам нужно изменить параметры SEQUENCE, которые Вы создали ранее, или они были созданы по умолчанию, и Вас не устраивают, то для их изменения необходимо просто указать эти параметры в инструкции ALTER SEQUENCE с новыми значениями.
Например, допустим Вы создали последовательность со значениями по умолчанию, а потом захотели уточнить минимальное, максимальное и начальное значение. Для этого Вы можете написать следующую инструкцию, в которой начальное значение, шаг приращения и минимальное значение будет 1, а максимальное 50.
ALTER SEQUENCE TestSequence RESTART WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;
Перезапуск последовательности
Если Вам необходимо перезапустить последовательность, т.е. сделать так чтобы значения начались с самого начала или с определенного значения, то Вы можете выполнить следующую инструкцию.
ALTER SEQUENCE TestSequence RESTART WITH 1;
где 1 – это новое начальное значение последовательности.
Примеры использование SEQUENCE
Создавать и изменять объект SEQUENCE мы научились, теперь давайте научимся использовать его для получения значения последовательности.
Обратиться к последовательности и получить следующее значение этой последовательности можно с помощью функции NEXT VALUE FOR, которую можно использовать даже в простом запросе SELECT.
Примечание! Функцию NEXT VALUE FOR нельзя использовать в запросах с операторами DISTINCT, UNION, UNION ALL, EXCEPT, INTERSECT, а также с инструкциями FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY и FOR XML.
Простое обращение к SEQUENCE
SELECT NEXT VALUE FOR TestSequence; SELECT NEXT VALUE FOR TestSequence; SELECT NEXT VALUE FOR TestSequence;
Использование SEQUENCE с INSERT
Сейчас давайте посмотрим, как можно использовать SEQUENCE в инструкции INSERT для генерирования значения для столбца идентификаторов в таблице.
Исходные данные для примеров
Для выполнения примеров нам потребуется таблица, в которую мы будет вставлять новые строки. В примерах будет использована таблица, созданная следующей инструкцией (для каждого примера данная таблица пересоздавалась).
CREATE TABLE Goods( ProductId INT NOT NULL, ProductName VARCHAR(100) NOT NULL );
Инструкция INSERT INTO с использованием SEQUENCE
Следующая инструкция вставляет 3 строки данных в таблицу Goods, значение ProductId мы берем из последовательности TestSequence с помощью функции NEXT VALUE FOR.
INSERT INTO Goods VALUES (NEXT VALUE FOR TestSequence, 'Компьютер'), (NEXT VALUE FOR TestSequence, 'Монитор'), (NEXT VALUE FOR TestSequence, 'Клавиатура'); SELECT * FROM Goods;
Чуть ниже я покажу результат выполнения этой инструкции в различных ситуациях, в каждой из которой SEQUENCE создавался с разным набором параметров. Тем самым, я думаю, будет наглядно видно, как работает объект последовательности с тем или иным параметром.
SEQUENCE создана со значениями по умолчанию
Как видите у нас возникла ошибка, это произошло потому, что мы не указали никаких параметров последовательности, все они были созданы со значениями по умолчанию.
Так, тип данных значения последовательности по умолчанию BIGINT, отсюда начальное значение последовательности очень большое (отрицательное) и оно не входит в диапазон значений типа данных INT, а именно этот тип данных имеет столбец ProductId в таблице Goods.
В результате мы имеем ошибку арифметического переполнения.
SEQUENCE создана с начальным значением 1 и увеличением на 1
SEQUENCE создана с начальным значением 10 и увеличением на 5
SEQUENCE создана с начальным значением 0 и уменьшением на 1
SEQUENCE создана с заданного числа и увеличением на 1
Здесь мы указали 12345 в качестве начального значения последовательности.
SEQUENCE создана с указанием максимального значения
CREATE SEQUENCE TestSequence AS INT START WITH 49 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;
В данном случае у нас возникла ошибка, так как при создании последовательности мы указали максимальное значение последовательности 50, а начальное значение 49, т.е. нам доступно всего два значения. Тем самым, когда мы пытаемся вставить третью строку, последовательность уже достигла своего максимального значения и возвращать ей просто нечего, возникает ошибка.
SEQUENCE создана с поддержкой цикла
Чтобы ошибка, которая у нас возникла выше, не появлялась, мы можем указать параметр CYCLE, тем самым, как только последовательность достигнет своего максимального значения, она будет перезапущена.
Однако здесь, конечно же, стоит учитывать возможность появления одних и тех же значений, ведь последовательность будет возвращать ровно те же самые значения, начиная с минимального.
CREATE SEQUENCE TestSequence AS INT START WITH 49 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50 CYCLE;
Удаление SEQUENCE
Удаляется последовательность, так же, как и другие объекты в SQL Server, с помощью инструкции DROP. Таким образом, для удаления последовательности необходимо написать DROP SEQUENCE. Параметр IF EXISTS у инструкции DROP также можно использовать (IF EXISTS доступен, начиная с 2016 версии SQL Server).
Например, для удаления нашей тестовой последовательности можно использовать следующую инструкцию
DROP SEQUENCE IF EXISTS TestSequence;
Заключение
На сегодня это все, в следующих материалах я расскажу о том, чем отличается объект последовательности SEQUENCE от свойства IDENTITY, которое также дает нам возможность генерировать уникальные идентификаторы записей.