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

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

创建一个序列对象并指定其属性。Creates a sequence object and specifies its properties. 序列是用户定义的绑定到架构的对象,该对象可根据创建序列所依据的规范来生成数值序列。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 can be configured to restart (cycle) when exhausted. 序列不与特定表相关联,这一点与标识列不同。Sequences, unlike identity columns, are not associated with specific tables. 应用程序将引用某一序列对象以便检索其下一个值。Applications refer to a sequence object to retrieve 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 across multiple rows and tables.

与在插入行时生成的标识列值不同,应用程序可以获得下一个序列号,而不必通过调用 NEXT VALUE FOR 函数来插入行。Unlike identity columns values that are generated when rows are inserted, an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function. 使用 sp_sequence_get_range 同时获取多个序列号。Use sp_sequence_get_range to get multiple sequence numbers at once.

有关同时使用 CREATE SEQUENCENEXT VALUE FOR 函数的信息,请参阅 序列号For information and scenarios that use both CREATE SEQUENCE and the NEXT VALUE FOR function, see Sequence Numbers.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

CREATE SEQUENCE [schema_name . ] sequence_name  
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
    [ START WITH <constant> ]  
    [ INCREMENT BY <constant> ]  
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <constant> ] } | { NO CACHE } ]  
    [ ; ]  

参数Arguments

sequence_name sequence_name
指定数据库中标识序列的唯一名称。Specifies the unique name by which the sequence is known in the database. 类型为 sysname 。Type is sysname.

[ built_in_integer_type | user-defined_integer_type[ built_in_integer_type | user-defined_integer_type
序列可定义为任何整数类型。A sequence can be defined as any integer type. 允许使用下面的类型。The following types are allowed.

  • tinyint - 范围为 0 到 255tinyint - Range 0 to 255
  • smallint - 范围为 -32,768 到 32,767smallint - Range -32,768 to 32,767
  • int - 范围为 -2,147,483,648 到 2,147,483,647int - Range -2,147,483,648 to 2,147,483,647
  • bigint - 范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • decimalnumeric,小数位数为 0。decimal and numeric with a scale of 0.
  • 基于这些允许类型之一的任何用户定义数据类型(别名类型)。Any user-defined data type (alias type) that is based on one of the allowed types.

如果未提供任何数据类型,则将 bigint 数据类型用作默认数据类型 。If no data type is provided, the bigint data type is used as the default.

START WITH <constant>START WITH <constant>
序列对象返回的第一个值。The first value returned by the sequence object. START 值必须小于或等于序列对象的最大值并大于或等于其最小值 。The START value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. 新序列对象的默认起始值是升序序列对象的最小值和降序序列对象的最大值。The default start value for a new sequence object is the minimum value for an ascending sequence object and the maximum value for a descending sequence object.

INCREMENT BY <constant>INCREMENT BY <constant>
每次调用 NEXT VALUE FOR 函数时序列对象值递增(如果为负数,则为递减)的值 。Value used to increment (or decrement if negative) the value of the sequence object for each call to the NEXT VALUE FOR function. 如果增量是负值,则序列对象为降序,否则为升序。If the increment is a negative value, the sequence object is descending; otherwise, it is ascending. 增量不能为 0。The increment cannot be 0. 新序列对象的默认增量为 1。The default increment for a new sequence object is 1.

[ MINVALUE <constant> | NO MINVALUE ] [ MINVALUE <constant> | NO MINVALUE ]
指定序列对象的边界。Specifies the bounds for the sequence object. 一个新序列对象的默认最小值是该序列对象的数据类型的最小值。The default minimum value for a new sequence object is the minimum value of the data type of the sequence object. 对于 tinyint 数据类型,此值为零,对于所有其他数据类型则为负数。This is zero for the tinyint data type and a negative number for all other data types.

[ MAXVALUE <constant> | NO MAXVALUE [ MAXVALUE <constant> | NO MAXVALUE
指定序列对象的边界。Specifies the bounds for the sequence object. 一个新序列对象的默认最大值是该序列对象的数据类型的最大值。The default maximum value for a new sequence object is the maximum value of the data type of the sequence object.

[ CYCLE | NO CYCLE ] [ CYCLE | NO CYCLE ]
此属性指定当超过序列对象的最小值或最大值时,序列对象是应从最小值(对于降序序列对象,则为最大值)重新开始,还是应引发异常。Property that specifies whether the sequence object should restart from the minimum value (or maximum for descending sequence objects) or throw an exception when its minimum or maximum value is exceeded. 新序列对象的默认循环选项是 NO CYCLE。The default cycle option for new sequence objects is NO CYCLE.

备注

循环 SEQUENCE 从最小值或最大值(而不是从起始值)重新开始。Cycling a SEQUENCE restarts from the minimum or maximum value, not from the start value.

[ CACHE [<constant> ] | NO CACHE ] [ CACHE [<constant> ] | NO CACHE ]
通过最大限度地减少生成序列编号所需的磁盘 IO 数,可以提高使用序列对象的应用程序的性能。Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers. 默认值为 CACHE。Defaults to CACHE.

例如,如果选择的缓存大小为 50,SQL ServerSQL Server 并不会缓存 50 个单个值。For example, if a cache size of 50 is chosen, SQL ServerSQL Server does not keep 50 individual values cached. 它只是缓存当前值和缓存中保留的值数。It only caches the current value and the number of values left in the cache. 这意味着,存储缓存所需的内存量始终为序列对象的数据类型的两个实例。This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

备注

如果启用缓存选项而不指定缓存大小,数据库引擎将选择一个大小。If the cache option is enabled without specifying a cache size, the Database Engine will select a size. 但是,所选的大小并不是一成不变。However, users should not rely upon the selection being consistent. MicrosoftMicrosoft 可能会更改计算缓存大小的方法而不另行通知。might change the method of calculating the cache size without notice.

当使用 CACHE 选项创建时,意外关机(如电源故障)可能导致缓存中保留的序列号丢失 。When created with the CACHE option, an unexpected shutdown (such as a power failure) may result in the loss of sequence numbers remaining in the cache.

一般备注General Remarks

序列号在当前事务的作用域之外生成。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 and is expected behavior.

缓存管理Cache management

为了提高性能,SQL ServerSQL Server 会预分配 CACHE 参数所指定数量的序列号 。To improve performance, SQL ServerSQL Server pre-allocates the number of sequence numbers specified by the CACHE argument.

例如,用起始值 1 和缓存大小 15 创建一个新序列。For an example, a new sequence is created with a starting value of 1 and a cache size of 15. 在需要第一个值时,便可从内存中使用值 1 到 15。When the first value is needed, values 1 through 15 are made available from memory. 最后缓存的值 (15) 将写入磁盘上的系统表中。The last cached value (15) is written to the system tables on the disk. 使用完所有的 15 个数字后,下一个请求(数字 16)将导致重新分配缓存。When all 15 numbers are used, the next request (for number 16) will cause the cache to be allocated again. 新的最后缓存值 (30) 将写入系统表中。The new last cached value (30) will be written to the system tables.

如果在使用了 22 个数字后数据库引擎Database Engine停止,则会将内存中的下一个预期序列号 (23) 写入系统表,替换以前存储的数字。If the 数据库引擎Database Engine is stopped after you use 22 numbers, the next intended sequence number in memory (23) is written to the system tables, replacing the previously stored number.

在 SQL Server 重新启动并且需要一个序列号之后,将从系统表中读取起始编号 (23)。After SQL Server restarts and a sequence number is needed, the starting number is read from the system tables (23). 会为内存分配 15 个数字 (23-38) 的缓存量,并将下一个非缓存编号 (39) 写入到系统表。The cache amount of 15 numbers (23-38) is allocated to memory and the next non-cache number (39) is written to the system tables.

如果数据库引擎Database Engine因某事件(如电源故障)异常停止,序列将使用从系统表中读取的编号 (39) 重新开始。If the 数据库引擎Database Engine stops abnormally for an event such as a power failure, the sequence restarts with the number read from system tables (39). 分配给内存(但用户或应用程序永远不会请求)的任何序列号都将丢失。Any sequence numbers allocated to memory (but never requested by a user or application) are lost. 此功能可能会留有间隙,但可以保证不会两次为单个序列对象发出同一个值,除非该对象定义为 CYCLE 或手动重新启动 。This functionality may leave gaps, but guarantees that the same value will never be issued two times for a single sequence object unless it is defined as CYCLE or is manually restarted.

将通过跟踪当前值(发出的最后一个值)和缓存中留下的值数量来在内存中保留缓存。The cache is maintained in memory by tracking the current value (the last value issued) and the number of values left in the cache. 因此,缓存所使用的内存量始终为序列对象的数据类型的两个实例。Therefore, the amount of memory used by the cache is always two instances of the data type of the sequence object.

如果将缓存参数设置为 NO CACHE,则每次使用序列时都会将当前的序列值写入系统表 。Setting the cache argument to NO CACHE writes the current sequence value to the system tables every time that a sequence is used. 这样会增加磁盘访问,从而降低性能,但可以减少出现意外间隙的机会。This might slow performance by increasing disk access, but reduces the chance of unintended gaps. 如果使用 NEXT VALUE FOR 或 sp_sequence_get_range 函数请求编号,但之后不使用该编号或在未提交的事务中使用该编号,那么仍然会出现间隙 。Gaps can still occur if numbers are requested using the NEXT VALUE FOR or sp_sequence_get_range functions, but then the numbers are either not used or are used in uncommitted transactions.

当序列对象使用 CACHE 选项时,如果重新启动该序列对象或改变 INCREMENT、CYCLE、MINVALUE、MAXVALUE 或缓存大小属性,则会导致在更改发生之前将缓存写入系统表 。When a sequence object uses the CACHE option, if you restart the sequence object, or alter the INCREMENT, CYCLE, MINVALUE, MAXVALUE, or the cache size properties, it will cause the cache to be written to the system tables before the change occurs. 然后,从当前值(即没有跳过任何数字)开始重新加载缓存。Then the cache is reloaded starting with the current value (i.e. no numbers are skipped). 更改缓存大小将立即生效。Changing the cache size takes effect immediately.

缓存的值可用时的 CACHE 选项CACHE option when cached values are available

每当请求序列对象以便为 CACHE 选项生成下一个值时,如果在序列对象的内存中缓存中提供未使用的值,则会发生以下过程 。The following process occurs every time that a sequence object is requested to generate the next value for the CACHE option if there are unused values available in the in-memory cache for the sequence object.

  1. 计算序列对象的下一个值。The next value for the sequence object is calculated.

  2. 在内存中更新该序列对象的新当前值。The new current value for the sequence object is updated in memory.

  3. 将计算的值返回给调用语句。The calculated value is returned to the calling statement.

缓存用尽时的 CACHE 选项CACHE option when the cache is exhausted

每当请求序列对象以便为 CACHE 选项生成下一个值时,如果缓存已用完,都会发生以下过程 :The following process occurs every time a sequence object is requested to generate the next value for the CACHE option if the cache has been exhausted:

  1. 计算序列对象的下一个值。The next value for the sequence object is calculated.

  2. 计算新缓存的最后一个值。The last value for the new cache is calculated.

  3. 锁定序列对象的系统表行,将在步骤 2 中计算的值(最后一个值)写入系统 表。The system table row for the sequence object is locked, and the value calculated in step 2 (the last value) is written to the system table. 触发缓存已用完的 xevent,向用户通知新的持久值。A cache-exhausted xevent is fired to notify the user of the new persisted value.

NO CACHE 选项NO CACHE option

每当请求序列对象以便为 NO CACHE 选项生成下一个值时,都会发生以下过程 :The following process occurs every time that a sequence object is requested to generate the next value for the NO CACHE option:

  1. 计算序列对象的下一个值。The next value for the sequence object is calculated.

  2. 将序列对象的新当前值写入系统表。The new current value for the sequence object is written to the system table.

  3. 将计算的值返回给调用语句。The calculated value is returned to the calling statement.

元数据Metadata

有关序列的信息,请查询 sys.sequencesFor information about sequences, query sys.sequences.

SecuritySecurity

权限Permissions

要求对 SCHEMA 拥有 CREATE SEQUENCEALTERCONTROL 权限。Requires CREATE SEQUENCE, ALTER, or CONTROL permission on the SCHEMA.

  • db_owner 和 db_ddladmin 固定数据库角色的成员可以创建、更改和删除序列对象。Members of the db_owner and db_ddladmin fixed database roles can create, alter, and drop sequence objects.

  • db_owner 和 db_datawriter 固定数据库角色的成员可以通过使序列对象生成数字来对其进行更新。Members of the db_owner and db_datawriter fixed database roles can update sequence objects by causing them to generate numbers.

以下示例向用户 AdventureWorks\Larry 授予在 Test 架构中创建序列的权限。The following example grants the user AdventureWorks\Larry permission to create sequences in the Test schema.

GRANT CREATE SEQUENCE ON SCHEMA::Test TO [AdventureWorks\Larry]  

可以使用 ALTER AUTHORIZATION 语句转让序列对象的所有权 。Ownership of a sequence object can be transferred by using the ALTER AUTHORIZATION statement.

如果序列使用用户定义的数据类型,则序列的创建者必须对该类型拥有 REFERENCES 权限。If a sequence uses a user-defined data type, the creator of the sequence must have REFERENCES permission on the type.

审核Audit

若要审核 CREATE SEQUENCE,请监视 SCHEMA_OBJECT_CHANGE_GROUP 。To audit CREATE SEQUENCE, monitor the SCHEMA_OBJECT_CHANGE_GROUP.

示例Examples

有关创建序列和使用 NEXT VALUE FOR 函数生成序列号的示例,请参阅序列号For examples of creating sequences and using the NEXT VALUE FOR function to generate sequence numbers, see Sequence Numbers.

下面的大多数示例都在名为 Test 的架构中创建序列对象。Most of the following examples create sequence objects in a schema named Test.

若要创建 Test 架构,请执行以下语句。To create the Test schema, execute the following statement.

CREATE SCHEMA Test ;  
GO  

A.A. 创建按 1 递增的序列Creating a sequence that increases by 1

在以下示例中,Thierry 创建一个名为 CountBy1 的序列,每次使用该序列时将增加 1。In the following example, Thierry creates a sequence named CountBy1 that increases by one every time that it is used.

CREATE SEQUENCE Test.CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  

B.B. 创建按 1 递减的序列Creating a sequence that decreases by 1

以下示例从 0 开始,每次使用时递减 1。The following example starts at 0 and counts into negative numbers by one every time it is used.

CREATE SEQUENCE Test.CountByNeg1  
    START WITH 0  
    INCREMENT BY -1 ;  
GO  

C.C. 创建按 5 递增的序列Creating a sequence that increases by 5

以下示例创建一个每次使用时增加 5 的序列。The following example creates a sequence that increases by 5 every time it is used.

CREATE SEQUENCE Test.CountBy1  
    START WITH 5  
    INCREMENT BY 5 ;  
GO  

D.D. 创建以指定数字开始的序列Creating a sequence that starts with a designated number

在导入表后,Thierry 注意到所使用的最高 ID 号是 24,328。After importing a table, Thierry notices that the highest ID number used is 24,328. Thierry 需要将生成从 24,329 开始的数字的序列。Thierry needs a sequence that will generate numbers starting at 24,329. 下面的代码将创建一个从 24,329 开始、增量为 1 的序列。The following code creates a sequence that starts with 24,329 and increments by 1.

CREATE SEQUENCE Test.ID_Seq  
    START WITH 24329  
    INCREMENT BY 1 ;  
GO  

E.E. 使用默认值创建序列Creating a sequence using default values

以下示例将使用默认值创建一个序列。The following example creates a sequence using the default values.

CREATE SEQUENCE Test.TestSequence ;  

执行以下语句可查看序列的属性。Execute the following statement to view the properties of the sequence.

SELECT * FROM sys.sequences WHERE name = 'TestSequence' ;  

输出的部分列表演示了默认值。A partial list of the output demonstrates the default values.

start_value -9223372036854775808
increment 1
mimimum_value -9223372036854775808
maximum_value 9223372036854775807
is_cycling 0
is_cached 1
current_value -9223372036854775808

F.F. 创建具有特定数据类型的序列Creating a sequence with a specific data type

以下示例使用 smallint 数据类型(范围为 -32,768 到 32,767)创建一个序列 。The following example creates a sequence using the smallint data type, with a range from -32,768 to 32,767.

CREATE SEQUENCE SmallSeq 
    AS smallint ;  

G.G. 使用所有参数创建序列Creating a sequence using all arguments

以下示例使用 decimal 数据类型(范围为 0 到 255)创建一个名为 DecSeq 的序列 。The following example creates a sequence named DecSeq using the decimal data type, having a range from 0 to 255. 序列以 125 开始,每次生成数字时递增 25。The sequence starts with 125 and increments by 25 every time that a number is generated. 因为该序列配置为可循环,所以,当值超过最大值 200 时,序列将从最小值 100 重新开始。Because the sequence is configured to cycle when the value exceeds the maximum value of 200, the sequence restarts at the minimum value of 100.

CREATE SEQUENCE Test.DecSeq  
    AS decimal(3,0)   
    START WITH 125  
    INCREMENT BY 25  
    MINVALUE 100  
    MAXVALUE 200  
    CYCLE  
    CACHE 3  
;  

执行以下语句可查看第一个值;START WITH 选项为 125。Execute the following statement to see the first value; the START WITH option of 125.

SELECT NEXT VALUE FOR Test.DecSeq;  

将该语句再执行三次,以返回 150、175 和 200。Execute the statement three more times to return 150, 175, and 200.

再次执行该语句,以查看起始值如何循环回到 MINVALUE 选项值 100。Execute the statement again to see how the start value cycles back to the MINVALUE option of 100.

执行以下代码,以确认缓存大小并查看当前值。Execute the following code to confirm the cache size and see the current value.

SELECT cache_size, current_value   
FROM sys.sequences  
WHERE name = 'DecSeq' ;  

另请参阅See Also

ALTER SEQUENCE (Transact-SQL) ALTER SEQUENCE (Transact-SQL)
DROP SEQUENCE (Transact-SQL) DROP SEQUENCE (Transact-SQL)
NEXT VALUE FOR (Transact-SQL) NEXT VALUE FOR (Transact-SQL)
序列号Sequence Numbers