一、Sequence簡介

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

二、Sequence基本概念

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

• 1、與identity列不同的是，Sequence插入表中的序列號(hào)可以被Update，除非通過觸發(fā)器來進(jìn)行保護(hù)
• 2、與identity列不同，Sequence有肯能插入重復(fù)值（Sequence可以設(shè)置循環(huán)，對于循環(huán)的Sequence來說會(huì)有重復(fù)值）
• 3、Sequence僅僅負(fù)責(zé)產(chǎn)生序列號(hào)，并不負(fù)責(zé)控制如何使用序列號(hào)，因此當(dāng)生成一個(gè)序列號(hào)被Rollback之后，Sequence會(huì)繼續(xù)生成下一個(gè)序列號(hào)，從而在序列號(hào)之間產(chǎn)生間隙

三、Sequence的用法

MSDN上對創(chuàng)建Sequence的語法如下：

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

 參數(shù)：

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

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

• 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

• 十進(jìn)制和數(shù)字為0的規(guī)模。

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

  如果沒有提供數(shù)據(jù)類型，則使用bigint數(shù)據(jù)類型作為默認(rèn)值。

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

  INCREMENT BY <constant> 
  用于每次調(diào)用NEXT VALUE FOR函數(shù)的序列對象的值遞增（或減少為負(fù)）的值。如果增量是負(fù)值，則序列對象是遞減的; 否則，它是升序。增量不能為0.新序列對象的默認(rèn)增量為1。

  [MINVALUE <constant> | NO MINVALUE ] 
  指定序列對象的邊界。新序列對象的默認(rèn)最小值是序列對象的數(shù)據(jù)類型的最小值。對于tinyint數(shù)據(jù)類型為零，所有其他數(shù)據(jù)類型為負(fù)數(shù)。

  [MAXVALUE <constant> | NO MAXVALUE
  指定序列對象的邊界。新序列對象的默認(rèn)最大值是序列對象的數(shù)據(jù)類型的最大值。

  [CYCLE | NO CYCLE ] 
  指定序列對象是否應(yīng)從最小值（或降序序列對象的最大值）重新啟動(dòng)的屬性，或者在超過最小值或最大值時(shí)引發(fā)異常。新序列對象的默認(rèn)循環(huán)選項(xiàng)為NO CYCLE。

  請注意，循環(huán)從最小值或最大值重新啟動(dòng)，而不是從起始值開始。

  [ CACHE [<constant>] | NO CACHE] 
  通過最小化生成序列號(hào)所需的磁盤IO數(shù)量，提高使用序列對象的應(yīng)用程序的性能。默認(rèn)為CACHE。

  例如，如果選擇緩存大小為50，則SQL Server不會(huì)保留緩存50個(gè)單獨(dú)的值。它只緩存當(dāng)前值和緩存中剩下的值的數(shù)量。這意味著存儲(chǔ)緩存所需的內(nèi)存量始終是序列對象的數(shù)據(jù)類型的兩個(gè)實(shí)例。

1、創(chuàng)建一個(gè)簡單的序列

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

2、查詢序列

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

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

3、使用序列

在單表中插入序列

在多表間共享序列號(hào)

--創(chuàng)建測試表1和測試表2
 CREATE table #test1
 (
   id bigint
 )
  CREATE table #test2
 (
   id bigint
 )
 --插入測試數(shù)據(jù)
 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
 --展示測試數(shù)據(jù)
 SELECT * FROM #test1
 SELECT * FROM #test2

結(jié)果如下圖所示：

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

--創(chuàng)建序列
CREATE sequence Seq_test1 --序列的名稱：Seq_test
as int --類型
start with 1 --開始值
increment by 1 --步長
minvalue 1 --最小值
maxvalue 5 --最大值
cycle --循環(huán)
--創(chuàng)建測試表
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
--查看結(jié)果
SELECT * FROM test1

查詢結(jié)果如下圖所示：

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

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

查詢結(jié)果如下圖所示：

Sequence一個(gè)需要注意的情況是Sequence只負(fù)責(zé)生成序列號(hào)，而不管序列號(hào)如何使用，如果事務(wù)不成功或回滾，SequenceNumber仍然會(huì)繼續(xù)向后生成序列號(hào)

我們還可以為Sequence指定緩存選項(xiàng)，使得減少IO，比如，我們指定Cache選項(xiàng)為3，則當(dāng)前的Sequence由1增長過3后，SQL Server會(huì)再分配3個(gè)空間變?yōu)閺?到6，當(dāng)分配到7時(shí)，SQL Server繼續(xù)這以循環(huán)，如果不指定Cache值，則值由SQL Server進(jìn)行分配。一個(gè)簡單的例子如圖所示。

到此這篇關(guān)于SQL Server中Sequence對象用法的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評論