一、Sequence簡介

Sequence對象對于Oracle用戶來說是最熟悉不過的數據庫對象了， 在SQL SERVER2012終于也可以看到這個對象了。Sequence是SQL Server2012推出的一個新特性。這個特性允許數據庫級別的序列號在多表或多列之間共享。

二、Sequence基本概念

Oracle中有Sequence的功能，SQL server類似的功能要使用identity列實現，但是identity列有很大的局限性。微軟終于在2012中添加了Sequence對象。與以往identity列不同的是：Sequence是一個 ?與架構綁定的數據庫級別的對象，而不是與具體的表的具體列所綁定。這就意味著Sequence帶來多表之間共享序列號的便利之外，還會帶來如下不利影響：

• 1、與identity列不同的是，Sequence插入表中的序列號可以被Update，除非通過觸發器來進行保護
• 2、與identity列不同，Sequence有肯能插入重復值（Sequence可以設置循環，對于循環的Sequence來說會有重復值）
• 3、Sequence僅僅負責產生序列號，并不負責控制如何使用序列號，因此當生成一個序列號被Rollback之后，Sequence會繼續生成下一個序列號，從而在序列號之間產生間隙

三、Sequence的用法

MSDN上對創建Sequence的語法如下：

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

?參數：

sequence_name
指定數據庫中已知序列的唯一名稱。類型是sysname。

[built_in_integer_type |?user-defined_integer_type?
可以將序列定義為任何整數類型。允許以下類型。

• tinyint?- 范圍0到255

• smallint?- 范圍-32,768至32,767

• int?- 范圍-2,147,483,648至2,147,483,647

• bigint?- 范圍-9,223,372,036,854,775,808至9,223,372,036,854,775,807

• 十進制和數字為0的規模。

• 基于允許類型之一的任何用戶定義的數據類型（別名類型）。

  如果沒有提供數據類型，則使用bigint數據類型作為默認值。

  START WITH ?
  序列對象返回的第一個值。START值必須小于一個值，或等于最大且大于或等于所述序列對象的最小值。新的序列對象的默認起始值??是升序序列對象的最小值和降序序列對象的最大值。

  INCREMENT BY ?
  用于每次調用NEXT VALUE FOR函數的序列對象的值遞增（或減少為負）的值。如果增量是負值，則序列對象是遞減的;?否則，它是升序。增量不能為0.新序列對象的默認增量為1。

  [MINVALUE |?NO MINVALUE?]?
  指定序列對象的邊界。新序列對象的默認最小值是序列對象的數據類型的最小值。對于tinyint數據類型為零，所有其他數據類型為負數。

  [MAXVALUE |?NO MAXVALUE
  指定序列對象的邊界。新序列對象的默認最大值是序列對象的數據類型的最大值。

  [CYCLE |?NO CYCLE?]?
  指定序列對象是否應從最小值（或降序序列對象的最大值）重新啟動的屬性，或者在超過最小值或最大值時引發異常。新序列對象的默認循環選項為NO CYCLE。

  請注意，循環從最小值或最大值重新啟動，而不是從起始值開始。

  [?CACHE?[] |?NO CACHE]?
  通過最小化生成序列號所需的磁盤IO數量，提高使用序列對象的應用程序的性能。默認為CACHE。

  例如，如果選擇緩存大小為50，則SQL Server不會保留緩存50個單獨的值。它只緩存當前值和緩存中剩下的值的數量。這意味著存儲緩存所需的內存量始終是序列對象的數據類型的兩個實例。

1、創建一個簡單的序列

CREATE sequence Seq_test --序列的名稱：Seq_test
as bigint --類型
start with 100000001 --開始值
increment by 1 --步長
minvalue 1 --最小值
maxvalue 999999999 --最大值
no cycle --不循環
cache --設置緩沖

2、查詢序列

創建了序列之后，可以通過SQL Server 2012新增的視圖sys.sequences來查看剛才創建成功的Sequence，如下圖所示：

--查看序列
SELECT * FROM sys.sequences WHERE name='Seq_test'

3、使用序列

在單表中插入序列

在多表間共享序列號

--創建測試表1和測試表2
 CREATE table #test1
 (
   id bigint
 )
  CREATE table #test2
 (
   id bigint
 )
 --插入測試數據
 DECLARE @index bigint
 SET @index=100000001
 WHILE (@index<100000005)
 begin
    insert INTO #test1(id) VALUES (NEXT value FOR Seq_test)
    insert INTO #test2(id) VALUES (NEXT value FOR Seq_test)
    SET @index=@index+1
 end
 --展示測試數據
 SELECT * FROM #test1
 SELECT * FROM #test2

結果如下圖所示：

在可以看到，如果我們不指定Sequence的上限和下限，則默認使用所指定數據類型的最大值和最小值作為上限和下限（INT類型的的上下限）.當達到上線后，可以指定循環來讓Sequence達到上限后從指定的開始值重新開始循環。

--創建序列
CREATE sequence Seq_test1 --序列的名稱：Seq_test
as int --類型
start with 1 --開始值
increment by 1 --步長
minvalue 1 --最小值
maxvalue 5 --最大值
cycle --循環
--創建測試表
CREATE table test1
(
  id int
)
DECLARE @index int
SET @index=0
WHILE(@index<10)
begin
   insert INTO test1(id) VALUES (NEXT value FOR Seq_test1)
   SET @index=@index+1
end
--查看結果
SELECT * FROM test1

查詢結果如下圖所示：

可以通過修改Sequence將其初始值指定為一個特定值

--修改序列的值
ALTER sequence Seq_test1
restart WITH 3
--查詢當前值
SELECT next value FOR Seq_test1

查詢結果如下圖所示：

Sequence一個需要注意的情況是Sequence只負責生成序列號，而不管序列號如何使用，如果事務不成功或回滾，SequenceNumber仍然會繼續向后生成序列號

我們還可以為Sequence指定緩存選項，使得減少IO，比如，我們指定Cache選項為3，則當前的Sequence由1增長過3后，SQL Server會再分配3個空間變為從4到6，當分配到7時，SQL Server繼續這以循環，如果不指定Cache值，則值由SQL Server進行分配。一個簡單的例子如圖所示。