目錄

• 一、概述
• 選擇使用序列的情況：
• 二、創(chuàng)建序列：CREATE SEQUENCE
• 二、使用序列號：NEXT VALUE FOR
• 1、序列值插入到表中
• 2、在select 語句中使用 NEXT VALUE FOR 。
• 3、通過使用 OVER 子句為結(jié)果集生成序列號
• 4、sp_sequence_get_range：同時獲取多個序列號
• 5、將表從標(biāo)識更改為序列
• 三、管理序列
• 1、更新（重置）序列：ALTER SEQUENCE
• 2、DROP SEQUENCE：刪除序列
• 3、查看序列信息

一、概述

SQL SERVER2012 之前版本，一般采用GUID或者IDENTITY來作為標(biāo)示符。在2012中，微軟終于增加了 SEQUENCE 對象，功能和性能都有了很大的提高。
序列是一種用戶定義的架構(gòu)綁定對象，它根據(jù)創(chuàng)建該序列時采用的規(guī)范生成一組數(shù)值。 這組數(shù)值以定義的間隔按升序或降序生成，并且可根據(jù)要求循環(huán)（重復(fù)）。

• 序列不與表相關(guān)聯(lián)，這一點與標(biāo)識列不同。
• 應(yīng)用程序?qū)⒁媚骋恍蛄袑ο笠员憬邮掌湎乱粋€值。
• 序列是通過使用 CREATE SEQUENCE 語句獨立于表來創(chuàng)建的。 其選項使您可以控制增量、最大值和最小值、起始點、自動重新開始功能和緩存以便改進性能。
• 與在插入行時生成的標(biāo)識列值不同，應(yīng)用程序可以通過調(diào)用 NEXT VALUE FOR 函數(shù)在插入行之前獲取下一序列號。 在調(diào)用 NEXT VALUE FOR 時分配該序列號，即使在該序列號永遠也不插入某個表中時也是如此。 此 NEXT VALUE FOR 函數(shù)可用作表定義中某個列的默認值。
• 使用 sp_sequence_get_range 可一次獲取某個范圍的多個序列號。
• 序列可定義為任何整數(shù)數(shù)據(jù)類型。 如tinyint, smallint, int, bigint, decimal 或是小數(shù)精度為0的數(shù)值類型。如果未指定數(shù)據(jù)類型，則序列將默認為 bigint。

序列的限制(limitation)有二個

• 序列不支持事務(wù)，即使事務(wù)中進行了回滾(rollback)操作，序列仍然返回下一個元素。
• 序列不支持SQL Server 復(fù)制(replication)，序列不會復(fù)制到訂閱的SQL Server實例中。如果一個表的默認值依賴一個序列，而序列又是不可復(fù)制的，這會導(dǎo)致訂閱的SQL Server出現(xiàn)腳本錯誤。

選擇使用序列的情況：

在以下情況下將使用序列，而非標(biāo)識列：

• 應(yīng)用程序要求在插入到表中之前有一個數(shù)值。
• 應(yīng)用程序要求在多個表之間或者某個表內(nèi)的多個列之間共享單個數(shù)值系列。
• 在達到指定的數(shù)值時，應(yīng)用程序必須重新開始該數(shù)值系列。 例如，在分配值 1 到 10 后，應(yīng)用程序再次開始分配值 1 到 10。
• 應(yīng)用程序要求序列值按其他字段排序。 NEXT VALUE FOR 函數(shù)可以將 OVER 子句應(yīng)用于該函數(shù)調(diào)用。 OVER 子句確保返回的值按照 OVER 子句的 ORDER BY 子句的順序生成。
• 應(yīng)用程序要求同時分配多個數(shù)值。 例如，應(yīng)用程序需要保留五個序號。 如果正在同時向其他進程發(fā)出數(shù)值，則請求標(biāo)識值可能會導(dǎo)致在系列中出現(xiàn)間斷。 調(diào)用 sp_sequence_get_range 可以一次檢索該序列中的若干數(shù)值。
• 您需要更改序列的規(guī)范，例如增量值。

二、創(chuàng)建序列：CREATE SEQUENCE

我們可以在SSMS中創(chuàng)建也可以使用SQL SERVER腳本創(chuàng)建序列對象：

1、使用默認值創(chuàng)建序列：若要創(chuàng)建從 -2,147,483,648 到 2,147,483,647 且增量為 1 的整數(shù)序列號。

CREATE SEQUENCE Schema.SequenceName      AS int      INCREMENT BY 1 ;

2、若要創(chuàng)建類似于從 1 到 2,147,483,647 且增量為 1 的標(biāo)識列的整數(shù)序列號，請使用以下語句。

CREATE SEQUENCE Schema.SequenceName      AS int      START WITH 1      INCREMENT BY 1 ;

3、使用所有參數(shù)創(chuàng)建序列

以下示例使用 decimal 數(shù)據(jù)類型（范圍為 0 到 255）創(chuàng)建一個名為 DecSeq 的序列 。 序列以 125 開始，每次生成數(shù)字時遞增 25。 因為該序列配置為可循環(huán)，所以，當(dāng)值超過最大值 200 時，序列將從最小值 100 重新開始。

CREATE SEQUENCE Test.DecSeq      AS decimal(3,0)       START WITH 125      INCREMENT BY 25      MINVALUE 100      MAXVALUE 200      CYCLE      CACHE 3  ;

二、使用序列號：NEXT VALUE FOR

執(zhí)行以下語句可查看第一個值；START WITH 選項為 125。將該語句再執(zhí)行三次，以返回 150、175 和 200。再次執(zhí)行該語句，以查看起始值如何循環(huán)回到 MINVALUE選項值 100。

SELECT NEXT VALUE FOR Test.DecSeq;

1、序列值插入到表中

下面的示例創(chuàng)建一個名為 Test 的架構(gòu)、一個名為 Orders 的表以及一個名為 CountBy1 的序列，然后使用 NEXT VALUE FOR 函數(shù)將行插入到該表中。

--Create the Test schema  CREATE SCHEMA Test ;  GO    -- Create a table  CREATE TABLE Test.Orders      (OrderID int PRIMARY KEY,      Name varchar(20) NOT NULL,      Qty int NOT NULL);  GO    -- Create a sequence  CREATE SEQUENCE Test.CountBy1      START WITH 1      INCREMENT BY 1 ;  GO    -- Insert three records  INSERT Test.Orders (OrderID, Name, Qty)      VALUES (NEXT VALUE FOR Test.CountBy1, "Tire", 2) ;  INSERT test.Orders (OrderID, Name, Qty)      VALUES (NEXT VALUE FOR Test.CountBy1, "Seat", 1) ;  INSERT test.Orders (OrderID, Name, Qty)      VALUES (NEXT VALUE FOR Test.CountBy1, "Brake", 1) ;  GO    -- View the table  SELECT * FROM Test.Orders ;  GO

下面是結(jié)果集：

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

2、在select 語句中使用 NEXT VALUE FOR 。

SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;

3、通過使用 OVER 子句為結(jié)果集生成序列號

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product  WHERE Name LIKE "%nut%" ;

4、sp_sequence_get_range：同時獲取多個序列號

從序列對象中返回一系列序列值。 序列對象生成和發(fā)出請求的值數(shù)目，并為應(yīng)用程序提供與該系列序列值相關(guān)的元數(shù)據(jù)。

以下語句從 RangeSeq 序列對象中獲取四個序列號，并向用戶返回過程中的所有輸出值。

DECLARE @range_first_value_output sql_variant ;    EXEC sys.sp_sequence_get_range  @sequence_name = N"Test.RangeSeq"  , @range_size = 4  , @range_first_value = @range_first_value_output OUTPUT ;    SELECT @range_first_value_output AS FirstNumber ;

5、將表從標(biāo)識更改為序列

下面的示例創(chuàng)建一個包含該示例的三行的架構(gòu)和表。 然后，該示例添加一個新列并且刪除舊列。

使用 Transact-SQL 的 SELECT * 語句將這個新列作為最后一列接收，而非作為第一列接收。 如果這樣做是不可接受的，則您必須創(chuàng)建全新的表，將數(shù)據(jù)移到該表中，然后針對這個新表重新創(chuàng)建權(quán)限。

-- 添加沒有IDENTITY屬性的新列ALTER TABLE Test.Department       ADD DepartmentIDNew smallint NULL  GO    -- 將值從舊列復(fù)制到新列  UPDATE Test.Department      SET DepartmentIDNew = DepartmentID ;  GO    -- 刪除舊列上的主鍵約束  ALTER TABLE Test.Department      DROP CONSTRAINT [PK_Department_DepartmentID];  -- 刪除舊列  ALTER TABLE Test.Department      DROP COLUMN DepartmentID ;  GO    -- 將新列重命名為舊列名  EXEC sp_rename "Test.Department.DepartmentIDNew",       "DepartmentID", "COLUMN";  GO    -- 將新列更改為NOT NULL  ALTER TABLE Test.Department      ALTER COLUMN DepartmentID smallint NOT NULL ;  -- 添加唯一的主鍵約束  ALTER TABLE Test.Department      ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED    (DepartmentID ASC) ;  -- 從DepartmentID列中獲取當(dāng)前的最高值，并創(chuàng)建一個用于列的序列。(返回3。) SELECT MAX(DepartmentID) FROM Test.Department ;  --使用下一個期望值(4)作為START WITH VALUE;  CREATE SEQUENCE Test.DeptSeq      AS smallint      START WITH 4      INCREMENT BY 1 ;  GO    -- 為DepartmentID列添加一個默認值  ALTER TABLE Test.Department      ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)   FOR DepartmentID;  GO    -- 查看結(jié)果  SELECT DepartmentID, Name, GroupName  FROM Test.Department ;   -- Test insert  INSERT Test.Department (Name, GroupName)  VALUES ("Audit", "Quality Assurance") ;  GO    -- 查看結(jié)果  SELECT DepartmentID, Name, GroupName  FROM Test.Department ;  GO

三、管理序列

1、更新（重置）序列：ALTER SEQUENCE

重新開始 Samples.IDLabel 序列。

ALTER SEQUENCE Samples.IDLabel  RESTART WITH 1 ;

2、DROP SEQUENCE：刪除序列

在生成編號后，序列對象與其生成的編號之間沒有延續(xù)關(guān)系，因此可以刪除序列對象，即使生成的編號仍在使用。

當(dāng)序列對象由存儲過程或觸發(fā)器引用時，可以刪除序列對象，因為序列對象未綁定到架構(gòu)上。 如果序列對象是作為表中的默認值引用的，則無法刪除序列對象。 錯誤消息將列出引用序列的對象。

以下示例從當(dāng)前數(shù)據(jù)庫中刪除一個名為 CountBy1 的序列對象。

DROP SEQUENCE CountBy1 ;

3、查看序列信息

有關(guān)序列的信息，請查詢 sys.sequences。

執(zhí)行以下代碼，以確認緩存大小并查看當(dāng)前值。

SELECT cache_size, current_value   FROM sys.sequences  WHERE name = "DecSeq" ;

到此這篇關(guān)于SQL Server序列SEQUENCE的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持。