序列号Sequence Numbers

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库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 SEQUENCEFor 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.

序列可定义为任何整数数据类型。A sequence can be defined as any integer data type. 如果未指定数据类型,则序列将默认为 bigintIf 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 子句确保返回的值按照 OVER 子句的 ORDER BY 子句的顺序生成。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. 若要防止更改序列值,请对表使用更新触发器以便回滚更改。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

若要创建从 -2,147,483,648 到 2,147,483,647 且增量为 1 的整数序列号,请使用以下语句。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 到 2,147,483,647 且增量为 1 的标识列的整数序列号,请使用以下语句。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.sequencesFor 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.B. 在插入某一行之前调用 NEXT VALUE FORCalling NEXT VALUE FOR before inserting a row

下面的示例通过使用在示例 A 中创建的 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.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.D. 在结果集中生成重复序列号Generating repeating sequence numbers in a result set

下面的示例演示序列号的两个功能:循环以及在 select 语句中使用 NEXT VALUE FORThe 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.E. 通过使用 OVER 子句为结果集生成序列号Generating 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.F. 重置序列号Resetting the sequence number

示例 E 使用了前 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.IDLabel 序列。Execute 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.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  
  

使用 SELECT *Transact-SQLTransact-SQL 语句将这个新列作为最后一列接收,而非作为第一列接收。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)