CREATE SEQUENCE: примеры (SQL)

Создание последовательностей в MS SQL с помощью CREATE SEQUENCE
Раздел: Функции для работы с последовательностями, DDL
CREATE_SEQUENCE(sequence_name ): N/A
типовые примерыCREATE SEQUENCE на mySQL, Oracle, PostgreSQL, SQLiteCREATE SEQUENCE изменения sqlпримеры расширенные

Описание функции CREATE SEQUENCE

Функция CREATE SEQUENCE в MS SQL Server предназначена для создания объекта последовательности, который генерирует последовательность числовых значений согласно заданным параметрам. Последовательности применяются для получения уникальных значений, часто в качестве суррогатных ключей.

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

Синтаксис функции включает несколько аргументов:

  • sequence_name - уникальное имя последовательности в рамках схемы.
  • [ AS ] data_type - тип данных последовательности. Допустимые типы: tinyint, smallint, int, bigint, decimal, numeric. По умолчанию используется bigint.
  • START WITH - начальное значение последовательности. Значение должно быть в диапазоне минимального и максимального значений. По умолчанию равно минимальному значению для возрастающей и максимальному для убывающей последовательности.
  • INCREMENT BY - шаг приращения последовательности. Положительное значение создает возрастающую последовательность, отрицательное - убывающую. Не может быть равным 0. По умолчанию равен 1.
  • { MINVALUE [ value ] } | { NO MINVALUE } - минимальное значение последовательности. Для типов decimal и numeric по умолчанию устанавливается минимальное значение типа. Для других типов NO MINVALUE эквивалентно минимальному значению типа.
  • { MAXVALUE [ value ] } | { NO MAXVALUE } - максимальное значение последовательности. Аналогично MINVALUE, по умолчанию зависит от типа данных.
  • { CYCLE | NO CYCLE } - определяет, будет ли последовательность перезапускаться после достижения предела. При CYCLE после достижения MAXVALUE для возрастающей последовательности начинается с MINVALUE. По умолчанию установлено NO CYCLE.
  • { CACHE [ size ] | NO CACHE } - указывает, сколько значений последовательности кэшируется в памяти для повышения производительности. По умолчанию SQL Server кэширует 50 значений.

Функция не возвращает значения. Результатом выполнения является создание объекта последовательности в базе данных.

Примеры использования CREATE SEQUENCE

Пример создания простой последовательности с параметрами по умолчанию:

CREATE SEQUENCE SimpleSequence;
Последовательность создана с типом bigint, START WITH = 1, INCREMENT BY = 1.

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

CREATE SEQUENCE OrderSequence
AS int
START WITH 1000
INCREMENT BY 5
MINVALUE 1000
MAXVALUE 9999
NO CYCLE
CACHE 10;
Последовательность создана с начальным значением 1000 и шагом 5.

Пример убывающей последовательности:

CREATE SEQUENCE DescSequence
START WITH 50
INCREMENT BY -1
MINVALUE 0
MAXVALUE 50
CYCLE
NO CACHE;
Последовательность создана с убыванием от 50 до 0 с последующим циклом.

Похожие функции в MS SQL

В MS SQL Server альтернативой CREATE SEQUENCE может служить использование IDENTITY свойства для столбцов таблицы. IDENTITY автоматически генерирует значения для столбца, но привязан к конкретной таблице. Последовательности более гибки, так как не зависят от таблиц.

Еще одна альтернатива - использование NEWID() или NEWSEQUENTIALID() для генерации уникальных идентификаторов GUID. Эти функции генерируют строковые значения, в отличие от числовых последовательностей.

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

Типичные ошибки

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

CREATE SEQUENCE ErrorSequence AS varchar(10);
Msg 402, Level 16, State 1, Line 1
Неправильный тип данных для параметра data_type.

Ошибка при указании START вне диапазона MINVALUE и MAXVALUE:

CREATE SEQUENCE RangeSequence
START WITH 20
MINVALUE 10
MAXVALUE 15;
Msg 11728, Level 16, State 1, Line 1
Начальное значение 20 выходит за пределы минимального и максимального значений.

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

CREATE SEQUENCE NoCycleSeq
START WITH 5
INCREMENT BY 1
MAXVALUE 5
NO CYCLE;
SELECT NEXT VALUE FOR NoCycleSeq; -- Выполнится
SELECT NEXT VALUE FOR NoCycleSeq; -- Ошибка
Msg 11728, Level 16, State 1, Line 5
Исчерпан предел для объекта последовательности.

Изменения в последних версиях

В SQL Server 2012 функция CREATE SEQUENCE была введена как новая функциональность. В последующих версиях существенных изменений не было. Однако в SQL Server 2017 улучшена производительность при использовании кэширования последовательностей.

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

Расширенные примеры

Создание последовательности для использования в нескольких таблицах:

Пример sql
CREATE SEQUENCE MultiTableSeq AS int START WITH 1 INCREMENT BY 1;

CREATE TABLE Table1 (ID int DEFAULT (NEXT VALUE FOR MultiTableSeq), Name varchar(50));
CREATE TABLE Table2 (ID int DEFAULT (NEXT VALUE FOR MultiTableSeq), Description varchar(50));

INSERT INTO Table1 (Name) VALUES ('Record1');
INSERT INTO Table2 (Description) VALUES ('Record2');
SELECT * FROM Table1;
SELECT * FROM Table2;
ID  Name
1   Record1

ID  Description
2   Record2

Использование последовательности в комбинации с OVER для нумерации строк:

Пример sql
CREATE SEQUENCE RowNumberSeq AS int START WITH 1 INCREMENT BY 1;

SELECT NEXT VALUE FOR RowNumberSeq OVER (ORDER BY Name) as RowNum, Name
FROM (VALUES ('Ivan'), ('Anna'), ('Petr')) AS Users(Name);
RowNum  Name
1       Anna
2       Ivan
3       Petr

Сброс последовательности с помощью ALTER SEQUENCE:

Пример sql
CREATE SEQUENCE ResetSeq START WITH 1;
SELECT NEXT VALUE FOR ResetSeq; -- 1
SELECT NEXT VALUE FOR ResetSeq; -- 2

ALTER SEQUENCE ResetSeq RESTART WITH 10;
SELECT NEXT VALUE FOR ResetSeq; -- 10

Аналоги в других СУБД

В Oracle используется аналогичная команда CREATE SEQUENCE с похожим синтаксисом. Отличия включают дополнительные параметры, такие как ORDER и NOCACHE, а также возможность использования последовательностей через NEXTVAL и CURRVAL.

CREATE SEQUENCE oracle_seq START WITH 1 INCREMENT BY 1 NOCACHE;

В PostgreSQL также существует CREATE SEQUENCE. Отличие состоит в том, что PostgreSQL автоматически создает последовательности для SERIAL столбцов.

CREATE SEQUENCE postgres_seq START 1 INCREMENT 1;

В MySQL отсутствует объект последовательности. Вместо этого используется AUTO_INCREMENT для столбцов таблицы, аналогично IDENTITY в SQL Server.

CREATE TABLE table1 (id INT AUTO_INCREMENT PRIMARY KEY);

В SQLite для автоинкремента используется ключевое слово AUTOINCREMENT, но оно работает только для целочисленного первичного ключа.

CREATE TABLE table1 (id INTEGER PRIMARY KEY AUTOINCREMENT);

MS SQL CREATE_SEQUENCE function comments

En
CREATE SEQUENCE Creates a sequence object and specifies its properties