Последовательность SQL-сервера

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

Что такое последовательность?

Последовательность — это просто список чисел, в котором важен их порядок. Например, {1,2,3} — это последовательность, а {3,2,1} — это совершенно другая последовательность.

В SQL Server последовательность — это определяемый пользователем объект, привязанный к схеме, который генерирует последовательность чисел в соответствии с указанной спецификацией. Последовательность числовых значений может быть в порядке возрастания или убывания с определенным интервалом и может циклически повторяться по запросу.

SQL Server оператор CREATE SEQUENCE

Чтобы создать новый объект последовательности, используйте оператор CREATE SEQUENCE следующим образом:

CREATE SEQUENCE [schema_name.] sequence_name  
    [ AS 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 } ];

Давайте рассмотрим синтаксис подробно:

имя_последовательности

Укажите уникальное имя последовательности в текущей базе данных.

AS целочисленный_тип

Используйте любой допустимый целочисленный тип для последовательности, например, TINYINT, SMALLINT, INT, BIGINT или DECIMAL и NUMERIC со шкалой 0. По умолчанию объект последовательности использует BIGINT.

НАЧАТЬ С начального_значения

Укажите первое значение, которое возвращает последовательность. Начальное_значение должно быть в диапазоне(min_value, max_value).

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

ПРИРАЩЕНИЕ НА increment_value

Укажите increment_value объекта последовательности при вызове функции NEXT VALUE FOR.

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

[ МИНЗНАЧЕНИЕ мин_значение | НЕТ МИНЗНАЧЕНИЯ ]

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

[ МАКС.ЗНАЧЕНИЕ макс._значение | НЕТ МАКС.ЗНАЧЕНИЯ]

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

[ ЦИКЛ | БЕЗ ЦИКЛА ]

Используйте CYCLE, если вы хотите, чтобы значение объекта последовательности перезапускалось с min_value для объекта восходящей последовательности или max_value для объекта нисходящей последовательности или выдавало исключение при превышении его min_value или max_value. SQL Server по умолчанию использует NO CYCLE для новых объектов последовательности.

[ КЭШ размер_кеша ] | БЕЗ КЭША ]

Укажите количество значений для кэширования, чтобы улучшить производительность последовательности, минимизируя количество дисковых операций ввода-вывода, необходимых для генерации номеров последовательности. По умолчанию SQL Server использует NO CACHE для новых объектов последовательности.

Примеры последовательностей SQL Server

Давайте рассмотрим несколько примеров создания последовательностей.

A) Создание простого примера последовательности

Следующий оператор использует оператор CREATE SEQUENCE для создания новой последовательности с именем item_counter с типом целого числа(INT), которая начинается с 10 и увеличивается на 10:

CREATE SEQUENCE item_counter
    AS INT
    START WITH 10
    INCREMENT BY 10;

Вы можете просмотреть объект последовательности в разделе Программируемость > Последовательности, как показано на следующем рисунке:

Следующий оператор возвращает текущее значение последовательности item_counter:

SELECT NEXT VALUE FOR item_counter;

Вот что получилось:

Current_value
-------------
10
(1 row affected)

В этом примере функция NEXT VALUE FOR генерирует порядковый номер из объекта последовательности item_counter.

Каждый раз, когда вы снова выполните следующий оператор, вы увидите, что значение item_counter будет увеличиваться на 10:

SELECT NEXT VALUE FOR item_counter;

На этот раз вывод такой:

Current_value
-------------
20
(1 row affected)    

Б) Пример использования объекта последовательности в одной таблице

Сначала создайте новую схему с именем «закупки»:

CREATE SCHEMA procurement;
GO

Далее создайте новую таблицу с именем orders:

CREATE TABLE procurement.purchase_orders(
    order_id INT PRIMARY KEY,
    vendor_id int NOT NULL,
    order_date date NOT NULL
);

Затем создайте новый объект последовательности с именем order_number, который начинается с 1 и увеличивается на 1:

CREATE SEQUENCE procurement.order_number 
AS INT
START WITH 1
INCREMENT BY 1;

После этого вставьте три строки в таблицу purchase.purchase_orders и используйте значения, сгенерированные последовательностью purchase.order_number:

INSERT INTO procurement.purchase_orders
   (order_id,
    vendor_id,
    order_date)
VALUES
   (NEXT VALUE FOR procurement.order_number,1,'2019-04-30');
INSERT INTO procurement.purchase_orders
   (order_id,
    vendor_id,
    order_date)
VALUES
   (NEXT VALUE FOR procurement.order_number,2,'2019-05-01');
INSERT INTO procurement.purchase_orders
   (order_id,
    vendor_id,
    order_date)
VALUES
   (NEXT VALUE FOR procurement.order_number,3,'2019-05-02');

Наконец, просмотрите содержимое таблицы purchase.purchase_orders:

SELECT 
    order_id, 
    vendor_id, 
    order_date
FROM 
    procurement.purchase_orders;

Вот что получилось:

C) Пример использования объекта последовательности в нескольких таблицах

Сначала создайте новый объект последовательности:

CREATE SEQUENCE procurement.receipt_no
START WITH 1
INCREMENT BY 1;

Во-вторых, создайте таблицы purchase.goods_receipts и purchase.invoice_receipts:

CREATE TABLE procurement.goods_receipts
(
    receipt_id   INT PRIMARY KEY 
        DEFAULT(NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    full_receipt BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100),
);
CREATE TABLE procurement.invoice_receipts
(
    receipt_id   INT PRIMARY KEY
        DEFAULT(NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    is_late      BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100)
);

Обратите внимание, что обе таблицы содержат receive_id, значения которого выводятся из последовательности purchase.receipt_no.

В-третьих, вставьте несколько строк в обе таблицы, не указывая значения для столбцов receive_id:

INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    1,
    '2019-05-12',
    'Goods receipt completed at warehouse'
);
INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-12',
    'Goods receipt has not completed at warehouse'
);
INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-13',
    'Invoice duly received'
);
INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    2,
    0,
    '2019-05-15',
    'Invoice duly received'
);

В-четвертых, запросите данные из обеих таблиц:

SELECT * FROM procurement.goods_receipts;
SELECT * FROM procurement.invoice_receipts;

Вот что получилось:

Последовательность и столбцы идентичности

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

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

Свойство/Особенность	Личность	Последовательность объектов
Разрешить указывать минимальные и/или максимальные значения приращения	Нет	Да
Разрешить сброс значения приращения	Нет	Да
Разрешить кэширование приращения значения генерации	Нет	Да
Разрешить указывать начальное значение приращения	Да	Да
Разрешить указывать значение приращения	Да	Да
Разрешить использование в нескольких таблицах	Нет	Да

Когда использовать последовательности

Объект последовательности используется вместо столбца идентификаторов в следующих случаях:

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

Получение информации о последовательностях

Для получения подробной информации о последовательностях используйте представление sys.sequences.

SELECT 
    * 
FROM 
    sys.sequences;

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