Порядковые номераSequence Numbers

ОБЛАСТЬ ПРИМЕНЕНИЯ: ДаSQL Server ДаБаза данных SQL Azure НетAzure Synapse Analytics (Хранилище данных SQL) НетParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

Последовательность представляет собой определяемый пользователем объект, привязанный к схеме, который формирует последовательность числовых значений в соответствии со спецификацией, с которой эта последовательность создавалась.A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. Последовательность числовых значений формируется в возрастающем или убывающем порядке с определенным интервалом и может повторяться запрошенным образом.The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. В отличие от столбцов идентификаторов последовательности не связаны с таблицами.Sequences, unlike identity columns, are not associated with tables. Приложение обращается к объекту последовательности, чтобы получить следующее значение.An application refers to a sequence object to receive its next value. Приложения управляют связями между последовательностями и таблицами.The relationship between sequences and tables is controlled by the application. Пользовательские приложения могут ссылаться на объект последовательности и координировать ключи значений между несколькими строками и таблицами.User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

Последовательность создается независимо от таблиц с помощью инструкции CREATE SEQUENCE .A sequence is created independently of the tables by using the CREATE SEQUENCE statement. Параметры позволяют управлять приращением, максимальным и минимальным значением, начальной точкой, возможностью автоматического перезапуска и кэшированием для повышения производительности.Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance. Сведения о параметрах см. в разделе CREATE SEQUENCE.For information about the options, see CREATE SEQUENCE.

В отличие от значений столбцов идентификаторов, которые создаются при вставке строк, приложение может получить следующий порядковый номер до вставки строки, вызвав функцию NEXT VALUE FOR .Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. Порядковый номер выделяется, когда вызывается функция NEXT VALUE FOR, даже если номер так и не вставляется в таблицу.The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table. Функцию NEXT VALUE FOR можно использовать в качестве значения по умолчанию для столбца в определении таблицы.The NEXT VALUE FOR function can be used as the default value for a column in a table definition. Сразу получить диапазон порядковых номеров можно с помощью процедуры sp_sequence_get_range .Use sp_sequence_get_range to get a range of multiple sequence numbers at once.

Последовательность может быть определена с любым типом данных integer.A sequence can be defined as any integer data type. Если тип данных не указан, по умолчанию для последовательности используется тип bigint.If the data type is not specified, a sequence defaults to bigint.

Использование последовательностейUsing Sequences

Последовательности используются вместо столбцов идентификаторов в следующих сценариях.Use sequences instead of identity columns in the following scenarios:

  • Приложению требуется номер до выполнения вставки в таблицу.The application requires a number before the insert into the table is made.

  • Приложению требуется единая нумерация для нескольких таблиц или нескольких столбцов в таблице.The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.

  • Приложение должно перезапускать последовательность номеров по достижении определенного номера.The application must restart the number series when a specified number is reached. Например, после назначения значений от 1 до 10 приложение вновь начинает назначать значения от 1 до 10.For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.

  • Приложению необходимо сортировать значения последовательности по другому полю.The application requires sequence values to be sorted by another field. Функция NEXT VALUE FOR может применять предложение OVER к вызову функции.The NEXT VALUE FOR function can apply the OVER clause to the function call. Предложение OVER гарантирует, что возвращаемые значения создаются в порядке, указанном предложением ORDER BY в предложении OVER.The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.

  • Приложению требуется одновременно назначать несколько номеров.An application requires multiple numbers to be assigned at the same time. Например, приложению требуется зарезервировать пять порядковых номеров.For example, an application needs to reserve five sequential numbers. Запрос значений идентификаторов может вызвать пропуски в последовательности, если другие процессы одновременно запросили номера.Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Вызов процедуры sp_sequence_get_range может получить несколько номеров в последовательности сразу.Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.

  • Необходимо изменить спецификацию последовательности, например значение приращения.You need to change the specification of the sequence, such as the increment value.

ОграниченияLimitations

В отличие от столбцов идентификаторов, значения которых нельзя изменять, значения последовательностей не защищаются автоматически после вставки в таблицу.Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. Чтобы запретить изменение значений последовательности, используйте в таблице триггер Update для отката изменений.To prevent sequence values from being changed, use an update trigger on the table to roll back changes.

Уникальность значений последовательности не соблюдается автоматически.Uniqueness is not automatically enforced for sequence values. Значения последовательностей изначально предусматривают многократное использование.The ability to reuse sequence values is by design. Если значения последовательности в таблице должны быть уникальными, создайте для столбца уникальный индекс.If sequence values in a table are required to be unique, create a unique index on the column. Если значения последовательности должны быть уникальными в пределах группы таблиц, создайте триггеры для исключения повторов, вызываемых инструкциями обновлений или циклической сменой порядковых номеров.If sequence values in a table are required to be unique throughout a group of tables, create triggers to prevent duplicates caused by update statements or sequence number cycling.

Объект последовательности создает номера в соответствии с определением, однако он не контролирует использование этих номеров.The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. В порядковых номерах, вставляемых в таблицу, могут возникать промежутки в случае отката транзакции, если объект последовательности совместно используется несколькими таблицами или если порядковые номера выделяются, но не используются в таблицах.Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables. Если создание производилось с параметром CACHE, то непредвиденное завершение (например, сбой питания) может привести к потере последовательных номеров в кэше.When created with the CACHE option, an unexpected shutdown, such as a power failure, can lose the sequence numbers in the cache.

Если несколько экземпляров функции NEXT VALUE FOR определяют один и тот же генератор последовательностей в одной инструкции Transact-SQLTransact-SQL , то все такие экземпляры возвращают одно и то же значение для строки, обрабатываемой этой инструкцией Transact-SQLTransact-SQL .If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQLTransact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQLTransact-SQL statement. Такое поведение согласуется со стандартом ANSI.This behavior is consistent with the ANSI standard.

Порядковые номера создаются вне области текущей транзакции.Sequence numbers are generated outside the scope of the current transaction. Они обрабатываются, когда выполняется фиксация или откат транзакции, использующей порядковый номер.They are consumed whether the transaction using the sequence number is committed or rolled back. Проверка на наличие повторов происходит, только если запись целиком заполнена.Duplicate validation only occurs once a record is fully populated. В некоторых случаях, когда одно число используется для создания нескольких записей, оно позже может считаться повтором.This can result in some cases where the same number is used for more than one record during creation, but then gets identified as a duplicate. Если это произошло и к последующим записям были применены другие значения автосчетчика, это может привести к различиям в значениях автосчетчика.If this occurs and other autonumber values have been applied to subsequent records, this can result in a gap between autonumber values.

Типичное применениеTypical Use

Чтобы создать целочисленный порядковый номер с приращением 1, меняющийся от -2 147 483 648 до 2 147 483 647, используйте следующую инструкцию.To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName  
    AS int  
    INCREMENT BY 1 ;  

Чтобы создать целочисленный порядковый номер, аналогичный столбцу идентификаторов с приращением 1, меняющемуся от 1 до 2 147 483 647, используйте следующую инструкцию.To create an integer sequence number similar to an identity column that increments by 1 from 1 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
  

Управление последовательностямиManaging Sequences

Чтобы получить сведения о последовательностях, запросите представление sys.sequences.For information about sequences, query sys.sequences.

ПримерыExamples

Дополнительные примеры см. в статьях CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL) и sp_sequence_get_range.There are additional examples in the topics CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL), and sp_sequence_get_range.

A.A. Использование порядкового номера в одной таблицеUsing a sequence number in a single table

В следующем примере создается схема с именем Test, таблица с именем Orders и последовательность с именем CountBy1, а затем строки вставляются в таблицу с помощью функции NEXT VALUE FOR.The following example creates a schema named Test, a table named Orders, and a sequence named CountBy1, and then inserts rows into the table using the NEXT VALUE FOR function.

--Create the Test schema  
CREATE SCHEMA Test ;  
GO  
  
-- Create a table  
CREATE TABLE Test.Orders  
    (OrderID int PRIMARY KEY,  
    Name varchar(20) NOT NULL,  
    Qty int NOT NULL);  
GO  
  
-- Create a sequence  
CREATE SEQUENCE Test.CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
-- Insert three records  
INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;  
INSERT test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;  
INSERT test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;  
GO  
  
-- View the table  
SELECT * FROM Test.Orders ;  
GO  

Результирующий набор:Here is the result set.

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

Б.B. Вызов NEXT VALUE FOR до вставки строкиCalling NEXT VALUE FOR before inserting a row

В следующем примере с помощью таблицы Orders , созданной в примере А, объявляется переменная с именем @nextID, а затем с помощью функции NEXT VALUE FOR этой переменной присваивается следующий доступный порядковый номер.Using the Orders table created in example A, the following example declares a variable named @nextID, and then uses the NEXT VALUE FOR function to set the variable to the next available sequence number. Предполагается, что в приложении выполняется некоторая обработка заказа, например заказчику сообщается номер OrderID потенциального заказа, а затем проводится проверка заказа.The application is presumed to do some processing of the order, such as providing the customer with the OrderID number of their potential order, and then validates the order. Независимо от времени, затрачиваемого на такую обработку, и от числа других заказов, добавляемых во время обработки, исходный номер сохраняется для использования в этом соединении.No matter how long this processing might take, or how many other orders are added during the process, the original number is preserved for use by this connection. Наконец, инструкция INSERT добавляет заказ в таблицу Orders .Finally, the INSERT statement adds the order to the Orders table.

DECLARE @NextID int ;  
SET @NextID = NEXT VALUE FOR Test.CountBy1;  
-- Some work happens  
INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (@NextID, 'Rim', 2) ;  
GO  
  

В.C. Использование порядкового номера в нескольких таблицахUsing a sequence number in multiple tables

В этом примере предполагается, что процесс мониторинга производственной линии получает уведомления о событиях, происходящих в цеху.This example assumes that a production-line monitoring process receives notification of events that occur throughout the workshop. Каждое событие получает уникальный, монотонно возрастающий номер EventID .Each event receives a unique and monotonically increasing EventID number. Все события используют один порядковый номер EventID , и поэтому отчеты, где объединяются все события, могут однозначно определить каждое событие.All events use the same EventID sequence number so that reports that combine all events can uniquely identify each event. Данные событий хранятся в трех различных таблицах в зависимости от типа события.However the event data is stored in three different tables, depending on the type of event. В примере кода создается схема с именем Audit, последовательность с именем EventCounterи три таблицы, каждая из которых использует последовательность EventCounter в качестве значения по умолчанию.The code example creates a schema named Audit, a sequence named EventCounter, and three tables which each use the EventCounter sequence as a default value. Затем в примере добавляются строки в три таблицы и запрашиваются результаты.Then the example adds rows to the three tables and queries the results.

CREATE SCHEMA Audit ;  
GO  
CREATE SEQUENCE Audit.EventCounter  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
CREATE TABLE Audit.ProcessEvents  
(  
    EventID int PRIMARY KEY CLUSTERED   
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EventCode nvarchar(5) NOT NULL,  
    Description nvarchar(300) NULL  
) ;  
GO  
  
CREATE TABLE Audit.ErrorEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EquipmentID int NULL,  
    ErrorNumber int NOT NULL,  
    EventDesc nvarchar(256) NULL  
) ;  
GO  
  
CREATE TABLE Audit.StartStopEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EquipmentID int NOT NULL,  
    StartOrStop bit NOT NULL  
) ;  
GO  
  
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)   
    VALUES (248, 0) ;  
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)   
    VALUES (72, 0) ;  
INSERT Audit.ProcessEvents (EventCode, Description)   
    VALUES (2735,   
    'Clean room temperature 18 degrees C.') ;  
INSERT Audit.ProcessEvents (EventCode, Description)   
    VALUES (18, 'Spin rate threashold exceeded.') ;  
INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)   
    VALUES (248, 82, 'Feeder jam') ;  
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)   
    VALUES (248, 1) ;  
INSERT Audit.ProcessEvents (EventCode, Description)   
    VALUES (1841, 'Central feed in bypass mode.') ;  
-- The following statement combines all events, though not all fields.  
SELECT EventID, EventTime, Description FROM Audit.ProcessEvents   
UNION SELECT EventID, EventTime, EventDesc FROM Audit.ErrorEvents   
UNION SELECT EventID, EventTime,   
CASE StartOrStop   
    WHEN 0 THEN 'Start'   
    ELSE 'Stop'  
END   
FROM Audit.StartStopEvents  
ORDER BY EventID ;  
GO  
  

Результирующий набор:Here is the result set.

EventID EventTime Description

1 2009-11-02 15:00:51.157 Start

2 2009-11-02 15:00:51.160 Start

3 2009-11-02 15:00:51.167 Clean room temperature 18 degrees C.

4 2009-11-02 15:00:51.167 Spin rate threshold exceeded.

5 2009-11-02 15:00:51.173 Feeder jam

6 2009-11-02 15:00:51.177 Stop

7 2009-11-02 15:00:51.180 Central feed in bypass mode.

Г.D. Создание повторяющихся порядковых номеров в результирующем набореGenerating repeating sequence numbers in a result set

В следующем примере показаны две возможности работы с порядковыми номерами: циклическое повторение и использование NEXT VALUE FOR в инструкции SELECT.The following example demonstrates two features of sequence numbers: cycling, and using NEXT VALUE FOR in a select statement.

CREATE SEQUENCE CountBy5  
   AS tinyint  
    START WITH 1  
    INCREMENT BY 1  
    MINVALUE 1  
    MAXVALUE 5  
    CYCLE ;  
GO  
  
SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;  
GO  

Д.E. Создание порядковых номеров для результирующего набора с помощью предложения OVERGenerating sequence numbers for a result set by using the OVER clause

В следующем примере предложение OVER используется для сортировки результирующего набора по столбцу Name перед добавлением столбца с порядковым номером.The following example uses the OVER clause to sort the result set by Name before it adds the sequence number column.

USE AdventureWorks2012 ;  
GO  
  
CREATE SCHEMA Samples ;  
GO  
  
CREATE SEQUENCE Samples.IDLabel  
    AS tinyint  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product  
WHERE Name LIKE '%nut%' ;  

Е.F. Сброс порядкового номераResetting the sequence number

В примере Д обработаны первые 79 порядковых номеров Samples.IDLabel.Example E consumed the first 79 of the Samples.IDLabel sequence numbers. (В используемой версии AdventureWorks2012 может возвращаться другое число результатов.) Чтобы обработать следующие 79 порядковых номеров (от 80 до 158), выполните следующий код.(Your version of AdventureWorks2012 may return a different number of results.) Execute the following to consume the next 79 sequence numbers (80 though 158).

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product  
WHERE Name LIKE '%nut%' ;  

Выполните следующую инструкцию, чтобы перезапустить последовательность Samples.IDLabelExecute the following statement to restart the Samples.IDLabel sequence.

ALTER SEQUENCE Samples.IDLabel  
RESTART WITH 1 ;  

Снова выполните инструкцию SELECT, чтобы убедиться, что последовательность Samples.IDLabel перезапущена с номера 1.Execute the select statement again to verify that the Samples.IDLabel sequence restarted with number 1.

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product  
WHERE Name LIKE '%nut%' ;  

Ж.G. Перевод таблицы с идентификаторов на последовательностьChanging a table from identity to sequence

В следующем примере создается схема и таблица, содержащая три строки.The following example creates a schema and table containing three rows for the example. Затем в примере добавляется новый столбец и удаляется старый столбец.Then the example adds a new column and drops the old column.

-- Create a schema  
CREATE SCHEMA Test ;  
GO  
  
-- Create a table  
CREATE TABLE Test.Department  
    (  
        DepartmentID smallint IDENTITY(1,1) NOT NULL,  
        Name nvarchar(100) NOT NULL,  
        GroupName nvarchar(100) NOT NULL  
    CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED   
         (DepartmentID ASC)   
    ) ;  
GO  
  
-- Insert three rows into the table  
INSERT Test.Department(Name, GroupName)  
    VALUES ('Engineering', 'Research and Development');  
GO  
  
INSERT Test.Department(Name, GroupName)  
    VALUES ('Tool Design', 'Research and Development');  
GO  
  
INSERT Test.Department(Name, GroupName)  
    VALUES ('Sales', 'Sales and Marketing');  
GO  
  
-- View the table that will be changed  
SELECT * FROM Test.Department ;  
GO  
  
-- End of portion creating a sample table  
--------------------------------------------------------  
-- Add the new column that does not have the IDENTITY property  
ALTER TABLE Test.Department   
    ADD DepartmentIDNew smallint NULL  
GO  
  
-- Copy values from the old column to the new column  
UPDATE Test.Department  
    SET DepartmentIDNew = DepartmentID ;  
GO  
  
-- Drop the primary key constraint on the old column  
ALTER TABLE Test.Department  
    DROP CONSTRAINT [PK_Department_DepartmentID];  
-- Drop the old column  
ALTER TABLE Test.Department  
    DROP COLUMN DepartmentID ;  
GO  
  
-- Rename the new column to the old columns name  
EXEC sp_rename 'Test.Department.DepartmentIDNew',   
    'DepartmentID', 'COLUMN';  
GO  
  
-- Change the new column to NOT NULL  
ALTER TABLE Test.Department  
    ALTER COLUMN DepartmentID smallint NOT NULL ;  
-- Add the unique primary key constraint  
ALTER TABLE Test.Department  
    ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED   
         (DepartmentID ASC) ;  
-- Get the highest current value from the DepartmentID column   
-- and create a sequence to use with the column. (Returns 3.)  
SELECT MAX(DepartmentID) FROM Test.Department ;  
-- Use the next desired value (4) as the START WITH VALUE;  
CREATE SEQUENCE Test.DeptSeq  
    AS smallint  
    START WITH 4  
    INCREMENT BY 1 ;  
GO  
  
-- Add a default value for the DepartmentID column  
ALTER TABLE Test.Department  
    ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)   
        FOR DepartmentID;  
GO  
  
-- View the result  
SELECT DepartmentID, Name, GroupName  
FROM Test.Department ;   
-- Test insert  
INSERT Test.Department (Name, GroupName)  
    VALUES ('Audit', 'Quality Assurance') ;  
GO  
  
-- View the result  
SELECT DepartmentID, Name, GroupName  
FROM Test.Department ;  
GO  
  

Инструкции Transact-SQLTransact-SQL, использующие SELECT *, будут получать новый столбец последним, а не первым.Transact-SQLTransact-SQL statements that use SELECT * will receive the new column as the last column instead of the first column. Если такая обработка нежелательна, необходимо создать новую таблицу, переместить в нее данные, а затем повторно создать разрешения для новой таблицы.If this is not acceptable, then you must create an entirely new table, move the data to it, and then recreate the permissions on the new table.

CREATE SEQUENCE (Transact-SQL)CREATE SEQUENCE (Transact-SQL)

ALTER SEQUENCE (Transact-SQL)ALTER SEQUENCE (Transact-SQL)

DROP SEQUENCE (Transact-SQL)DROP SEQUENCE (Transact-SQL)

Свойство IDENTITY (Transact-SQL)IDENTITY (Property) (Transact-SQL)