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

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) 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_namesequence_name
指定資料庫中順序的唯一識別名稱。Specifies the unique name by which the sequence is known in the database. 類型是 sysnameType 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
  • 小數位數為 0 的 decimalnumericdecimal 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.

注意

如果已啟用快取選項但未指定快取大小,Database Engine 將選取大小。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 EngineDatabase Engine 停止,記憶體中的下一個預定序號 (23) 會寫入至系統資料表,取代先前儲存的數字。If the Database EngineDatabase 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 EngineDatabase Engine 因停電等事件而異常停止,順序會以讀取自系統資料表的數字重新啟動 (39)。If the Database EngineDatabase 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 FORsp_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 選項時,如果您重新啟動順序物件,或改變 INCREMENTCYCLEMINVALUEMAXVALUE 或快取大小屬性,則會導致快取在發生變更之前寫入至系統資料表。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.

安全性Security

權限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_GROUPTo 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 的順序,此順序每次使用時遞增一。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 開始,並且每次使用時遞減一。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 發現使用的最高識別碼值是 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