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

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

修改现有序列对象的参数。Modifies the arguments of an existing sequence object. 如果该序列是使用 CACHE 选项创建的,则改变序列将重新创建缓存 。If the sequence was created with the CACHE option, altering the sequence will recreate the cache.

序列对象是通过使用 CREATE SEQUENCE 语句创建的。Sequences objects are created by using the CREATE SEQUENCE statement. 序列是整数值,可以是返回整数的任何数据类型。Sequences are integer values and can be of any data type that returns an integer. 使用 ALTER SEQUENCE 语句无法更改数据类型。The data type cannot be changed by using the ALTER SEQUENCE statement. 若要更改数据类型,请删除或创建序列对象。To change the data type, drop and create the sequence object.

序列对象是用户定义的绑定到架构的对象,用于可根据规范生成数值序列。A sequence is a user-defined schema bound object that generates a sequence of numeric values according to a specification. 通过调用 NEXT VALUE FOR 函数,从序列中生成新值。New values are generated from a sequence by calling the NEXT VALUE FOR function. 使用 sp_sequence_get_range 同时获取多个序列号。Use sp_sequence_get_range to get multiple sequence numbers at once. 有关同时使用 CREATE SEQUENCE、sp_sequence_get_range 和 NEXT VALUE FOR 函数的信息和方案,请参阅序列号For information and scenarios that use both CREATE SEQUENCE, sp_sequence_get_range, and the NEXT VALUE FOR function, see Sequence Numbers.

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

语法Syntax

  
ALTER SEQUENCE [schema_name. ] sequence_name  
    [ RESTART [ 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.

RESTART [ WITH <constant> ]RESTART [ WITH <constant> ]
将由序列对象返回的下一个值。The next value that will be returned by the sequence object. 如果提供,则 RESTART WITH 值必须是小于或等于序列对象的最大值并大于或等于其最小值的整数。If provided, the RESTART WITH value must be an integer that is less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. 如果忽略 WITH 值,则序列编号将根据原始 CREATE SEQUENCE 选项重新开始。If the WITH value is omitted, the sequence numbering restarts based on the original CREATE SEQUENCE options.

INCREMENT BY <constant>INCREMENT BY <constant>
用于在每次调用 NEXT VALUE FOR 函数时递增(如果为负数,则为递减)序列对象的基值的值。The value that is used to increment (or decrement if negative) the sequence object's base value 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 can not be 0.

[ MINVALUE <constant> | NO MINVALUE ][ MINVALUE <constant> | NO MINVALUE ]
指定序列对象的边界。Specifies the bounds for sequence object. 如果指定 NO MINVALUE,则使用序列数据类型的最小可能值。If NO MINVALUE is specified, the minimum possible value of the sequence data type is used.

[ MAXVALUE <constant> | NO MAXVALUE[ MAXVALUE <constant> | NO MAXVALUE
指定序列对象的边界。Specifies the bounds for sequence object. 如果指定 NO MAXVALUE,则使用序列数据类型的最大可能值。If NO MAXVALUE is specified, the maximum possible value of the sequence data type is used.

[ CYCLE | NO CYCLE ][ CYCLE | NO CYCLE ]
此属性指定当超过序列对象的最小值或最大值时,序列对象是应从最小值(对于降序对象,则为最大值)重新开始,还是应引发异常。This property 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.

备注

在循环后,下一个值为最小值或最大值,而不是序列的 START VALUE(起始值)。After cycling the next value is the minimum or maximum value, not the START VALUE of the sequence.

[ CACHE [<constant> ] | NO CACHE ][ CACHE [<constant> ] | NO CACHE ]
通过最大限度地减少将生成的值持久保存到系统表中所需的 IO 数,可以提高使用序列对象的应用程序的性能。Increases performance for applications that use sequence objects by minimizing the number of IOs that are required to persist generated values to the system tables.

有关缓存的行为的详细信息,请参阅 CREATE SEQUENCE (Transact-SQL)For more information about the behavior of the cache, see CREATE SEQUENCE (Transact-SQL).

RemarksRemarks

有关如何创建序列以及如何管理序列缓存的信息,请参阅 CREATE SEQUENCE (Transact-SQL)For information about how sequences are created and how the sequence cache is managed, see CREATE SEQUENCE (Transact-SQL).

不能将表示升序的 MINVALUE 和表示降序的 MAXVALUE 更改为不允许序列的 START WITH 值的值。The MINVALUE for ascending sequences and the MAXVALUE for descending sequences cannot be altered to a value that does not permit the START WITH value of the sequence. 如果要将升序的 MINVALUE 更改为一个大于 START WITH 值的数字,或者将降序的 MAXVALUE 更改为小于 START WITH 值的数字,请加入 RESTART WITH 参数,以便在最小值和最大值范围内的所需点重新开始序列。To change the MINVALUE of an ascending sequence to a number larger than the START WITH value or to change the MAXVALUE of a descending sequence to a number smaller than the START WITH value, include the RESTART WITH argument to restart the sequence at a desired point that falls within the minimum and maximum range.

元数据Metadata

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

SecuritySecurity

权限Permissions

要求对序列拥有 ALTER 权限或对架构拥有 ALTER 权限 。Requires ALTER permission on the sequence or ALTER permission on the schema. 若要授予针对序列的 ALTER 权限,请按以下格式使用 ALTER ON OBJECT 对象 :To grant ALTER permission on the sequence, use ALTER ON OBJECT in the following format:

GRANT ALTER ON OBJECT::Test.TinySeq TO [AdventureWorks\Larry]  

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

审核Audit

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

示例Examples

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

A.A. 更改序列Altering a sequence

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

CREATE SCHEMA Test ;  
GO  
  
CREATE SEQUENCE Test.TestSeq  
    AS int   
    START WITH 125  
    INCREMENT BY 25  
    MINVALUE 100  
    MAXVALUE 200  
    CYCLE  
    CACHE 3  
;  
GO  

下面的示例更改 TestSeq 序列,使范围介于 50 到 200 之间。The following example alters the TestSeq sequence to have a range from 50 to 200. 序列以 100 重新开始编号系列,每次生成数字时递增 50。The sequence restarts the numbering series with 100 and increments by 50 every time that a number is generated.

ALTER SEQUENCE Test. TestSeq  
    RESTART WITH 100  
    INCREMENT BY 50  
    MINVALUE 50  
    MAXVALUE 200  
    NO CYCLE  
    NO CACHE  
;  
GO  

因为此序列不循环,所以,当序列超过 200 时,NEXT VALUE FOR 函数将导致错误 。Because the sequence will not cycle, the NEXT VALUE FOR function will result in an error when the sequence exceeds 200.

B.B. 重新开始序列Restarting a sequence

以下示例将创建一个名为 CountBy1 的序列。The following example creates a sequence named CountBy1. 该序列使用默认值。The sequence uses the default values.

CREATE SEQUENCE Test.CountBy1 ;  

若要生成序列值,所有者随后应执行下列语句:To generate a sequence value, the owner then executes the following statement:

SELECT NEXT VALUE FOR Test.CountBy1  

返回的值 -9,223,372,036,854,775,808 是 bigint 数据类型的最小值 。The value returned of -9,223,372,036,854,775,808 is the lowest possible value for the bigint data type. 所有者认识到他原来希望序列从 1 开始,但创建序列时没有指定 START WITH 子句 。The owner realizes he wanted the sequence to start with 1, but did not indicate the START WITH clause when he created the sequence. 若要更正此错误,所有者应执行下面的语句。To correct this error, the owner executes the following statement.

ALTER SEQUENCE Test.CountBy1 RESTART WITH 1 ;  

然后,所有者再次执行下列语句以生成序列号。Then the owner executes the following statement again to generate a sequence number.

SELECT NEXT VALUE FOR Test.CountBy1;  

数字现在是 1,与预期相符。The number is now 1, as expected.

CountBy1 序列是使用默认值 NO CYCLE 创建的,因此,在生成数字 9,223,372,036,854,775,807 后,它将停止运行。The CountBy1 sequence was created using the default value of NO CYCLE so it will stop operating after generating number 9,223,372,036,854,775,807. 对序列对象的后续调用将返回错误 11728。Subsequent calls to the sequence object will return error 11728. 下面的语句将序列对象更改为循环,并将缓存设置为 20。The following statement changes the sequence object to cycle and sets a cache of 20.

ALTER SEQUENCE Test.CountBy1  
    CYCLE  
    CACHE 20 ;  
  

现在,当序列对象达到 9,223,372,036,854,775,807 时,它将循环,循环后的下一个编号将为此数据类型的最小值,即 -9,223,372,036,854,775,808。Now when the sequence object reaches 9,223,372,036,854,775,807 it will cycle, and the next number after cycling will be the minimum of the data type, -9,223,372,036,854,775,808.

所有者意识到每次使用 bigint 数据类型时都将占用 8 个字节 。The owner realized that the bigint data type uses 8 bytes each time it is used. 使用 4 个字节的 int 数据类型就足够了 。The int data type that uses 4 bytes is sufficient. 但是,不能更改序列对象的数据类型。However the data type of a sequence object cannot be altered. 若要更改为 int 数据类型,所有者必须删除序列对象,并使用正确的数据类型重新创建对象 。To change to an int data type, the owner must drop the sequence object and recreate the object with the correct data type.

另请参阅See Also

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