本文將介紹如何利用 SQL Server 2012 新的 SEQUENCE 物件來維護流水號。
在 SQL Server 2012 以前的版本，要產生流水號不外乎是在 TABLE中的欄位加上 IDENTITY 或是利用 IDENTITY 方法來產生流水號，如下列的 T-SQL 敘述：
1: --於欄位中使用 IDENTITY 來產生流水號
2: DECLARE @t TABLE
3: ( c1 int IDENTITY
4: ,c2 int
5: )
6:
7: INSERT INTO @t VALUES (10),(20),(30)
8: SELECT * FROM @t
9:
10: DELETE @t WHERE c2 = 20
11: INSERT INTO @t VALUES (20)
12: SELECT * FROM @t
13:
14:
15: --使用 IDENTITY 方法來產生流水號
16: IF EXISTS (SELECT * FROM sys.objects WHERE type = 'U' AND name = 'MyTABLE')
17: DROP TABLE MyTABLE
18: GO
19:
20: --產生名為MyTable的資料表，其中 c1 為 IDENTITY，c2 為 int 型態
21: SELECT IDENTITY(int,1,1) AS c1,0 as c2
22: INTO MyTABLE
23:
24: INSERT INTO MyTABLE(c2) VALUES (10),(20),(30)
25: SELECT * FROM MyTABLE
26:
27: DELETE MyTABLE WHERE c2 = 20
28: INSERT INTO MyTABLE(c2) VALUES (20)
29: SELECT * FROM MyTABLE
從上面的程式碼可知，利用 IDENTITY 產生的流水號必須在實際 INSERT 資料到該欄位時才會產生，而且用過的流水號無法再使用，除非透過 DBCC CHECKIDENT 來限制下一個流水號的起始號碼。
在 SQL Server 2012 可以利用新的 SEQUENCE 物件來維護流水號，不會因為刪除資料而產生跳號，您不需要像以往一樣自行建立 TABLE 來記錄流水號用到幾號，對於有流水號需求將會方便許多。
- 以下 T-SQL 用來建立 SEQUENCE 物件，起始編號為 1 號，每次增量為 2，最小值為 1，最大值為 10，流水號不循環，為減少磁碟 IO，這個範例筆者快取 3 個序號。
1: /*
2: CREATE SEQUENCE [schema_name . ] sequence_name
3: [ AS [ built_in_integer_type | user-defined_integer_type ] ]
4: [ START WITH <constant> ]
5: [ INCREMENT BY <constant> ]
6: [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
7: [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
8: [ CYCLE | { NO CYCLE } ]
9: [ { CACHE [ <constant> ] } | { NO CACHE } ]
10: [ ; ]
11: */
12:
13: IF EXISTS (SELECT * FROM sys.sequences WHERE name = 'MySeqNo')
14: DROP SEQUENCE MySeqNo
15: GO
16:
17: --建立 Sequence 物件
18: CREATE SEQUENCE MySeqNo
19: AS tinyint
20: START WITH 1
21: INCREMENT BY 2
22: MINVALUE 1
23: MAXVALUE 10
24: NO CYCLE
25: CACHE 3
26:
27: GO
28:
29: --查詢SEQUENCE物件
30: SELECT * FROM sys.sequences
31: GO
- 您可以利用下列 T-SQL 來取得 SEQUENCE 物件中的下一個流水號：
1: --使用SEQUENCE物件
2: SELECT NEXT VALUE FOR MySeqNo
- 當您嘗試從 MySeqNo 物件中取得流水號，將會得到 1、3、5、7、9等五個號碼，若在得到 9 之後再利用 NEXT VALUE FOR 取得下一個流水號時，會遇到如下圖的錯誤訊息：
- 您可以利用 ALTER SEQUENCE 來重設流水號，或是把 SEQUENCE 物件設定為自動重頭產生新的號碼。
1: --方法一：重設 MySequence，故意重新設定由2開始
2: ALTER SEQUENCE MySeqNo
3: RESTART WITH 2
4: GO
5:
6: --嘗試再取得流水號，預計應該是會得到 2
7: SELECT NEXT VALUE FOR MySeqNo
8: GO
9:
10: --方法二：設定為自動循環
11: ALTER SEQUENCE MySeqNo
12: CYCLE
13:
14: --因為設定為自動循環，所以重複取號多次也不會發生超出範圍的錯誤
15: SELECT NEXT VALUE FOR MySeqNo
16: GO 10
最後筆者以一個簡單的範例，嘗試舉例說明 SEQUENCE 物件可用來產生生符合特定商業規則的流水號。下列的 T-SQL 用來模擬產生發票號碼：
1: IF EXISTS (SELECT * FROM sys.sequences WHERE name = 'InvoiceSeqNo')
2: DROP SEQUENCE InvoiceSeqNo
3: GO
4:
5: CREATE SEQUENCE InvoiceSeqNo
6: AS tinyint
7: START WITH 1
8: INCREMENT BY 1
9: MINVALUE 1
10: MAXVALUE 10
11: NO CYCLE
12: CACHE 3
13:
14: GO
15:
16:
17: DECLARE @i TABLE
18: ( --產生規則為 AB+2位流水號的發票號碼
19: InvoiceNo char(10) DEFAULT ('AB'
20: + RIGHT('0' + CONVERT(VARCHAR(10),NEXT VALUE FOR InvoiceSeqNo),2))
21: ,Amount int
22: )
23:
24: INSERT INTO @i(Amount) VALUES (100),(200),(100),(500)
25: SELECT * FROM @i
26:
27: GO
【參考資料】
