順序物件

CREATE SEQUENCE 可以用來建立順序物件（sequence object ）。 順序物件是一個由使用者定義的物件，它會根據定義規格產生數值序列。 不同於新增資料時使用的識別資料行（identity），應用程式可以藉由呼叫 NEXT VALUE FOR 函數取得下一個序號，而不需要插入資料列。

語法

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 } ]
    [ ; ]

建立順序物件

例一：使用語法建立

CREATE SEQUENCE MySeqNo
	AS int
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 100
	NO CYCLE
	CACHE;

例二：使用介面建立

使用順序物件

順序物件可以使用在以下幾種狀況：

1. 重設序號

下列指令，可以用來取得序號、重置序號、設定自動循環

--重設 MySeqNo ,由10開始

ALTER SEQUENCE MySeqNo   
RESTART WITH 10

--取得流水號，預計應該是會得到 10

SELECT NEXT VALUE FOR MySeqNo

--因為設定為不循環，所以，若號碼超過100將發生錯誤

--設定為自動循環

ALTER SEQUENCE MySeqNo  
CYCLE

--因為設定為自動循環，取得流水號，預計應該是會得到 1

SELECT NEXT VALUE FOR MySeqNo


--在 INSERT 命令中直接取得流水號

INSERT tmpTable (ID,Name) VALUES ( NEXT VALUE FOR MySeqNo ,'test name' );

2. 在 INSERT 指令中使用順序物件

若要在新增陳述式中，直接取得流水號，你可以這麼做

INSERT tmpTable (ID,Name) VALUES ( NEXT VALUE FOR MySeqNo ,'test name' );

3. 將 SEQUENCE 當做欄位預設值

流水號物件也可以當做欄位的預設值，你只要加入一個預設值的條作約束即可。

ALTER TABLE [dbo].[tmpTable] 
	ADD CONSTRAINT [DF_tmpTable_ID] DEFAULT (NEXT VALUE FOR [dbo].[MySeqNo]) FOR [ID]

4. 在排序的結果集中，加入順序值

你也可以透過 NEXT VALUE FOR 的 OVER 子句，設定 sequence object 在回傳的結果集中，依指定的特定欄位排序後再給值。

--在回傳結果集中套用順序值
SELECT NEXT VALUE FOR MySeqNum as SeqNum, ProductID, ProductName 
FROM Products

--在回傳結果集中，先排序再套用順序值
SELECT NEXT VALUE FOR MySeqNum OVER ( ORDER BY ProductID ) as SeqNum, ProductID, ProductName 
FROM Products

使用順序的時機

雖然識別資料行（identity）也有自動建立順序數值的能力，但是下面幾種狀況下，應該使用 sequences ，而不是 識別資料行：

• 你須要在 INSERT 前就取得下一號編號。
• 你須要在多個資料表中共用一個序列號。
• 你須要一次就取得多個序列號。
• 當序列號使用到達特定數字後，你須要重新開始序列號。
• 你需要依特定欄位排序再產生序列號。
• 你需要可以變更序列號的規格時，例如將規格由每次加１號改成每次加２號。