SQL Server序列SEQUENCE用法介紹

2022-06-01 18:00:26

一、概述

SQL SERVER2012 之前版本，一般採用GUID或者IDENTITY來作為標示符。在2012中，微軟終於增加了 SEQUENCE 物件，功能和效能都有了很大的提高。
序列是一種使用者定義的架構繫結物件，它根據建立該序列時採用的規範生成一組數值。這組數值以定義的間隔按升序或降序生成，並且可根據要求迴圈（重複）。

序列不與表相關聯，這一點與標識列不同。
應用程式將參照某一序列物件以便接收其下一個值。
序列是通過使用 CREATE SEQUENCE 語句獨立於表來建立的。其選項使您可以控制增量、最大值和最小值、起始點、自動重新開始功能和快取以便改進效能。
與在插入行時生成的標識列值不同，應用程式可以通過呼叫 NEXT VALUE FOR 函數在插入行之前獲取下一序列號。在呼叫 NEXT VALUE FOR 時分配該序列號，即使在該序列號永遠也不插入某個表中時也是如此。此 NEXT VALUE FOR 函數可用作表定義中某個列的預設值。
使用 sp_sequence_get_range 可一次獲取某個範圍的多個序列號。
序列可定義為任何整數資料型別。如tinyint, smallint, int, bigint, decimal 或是小數精度為0的數值型別。如果未指定資料型別，則序列將預設為 bigint。

序列的限制(limitation)有二個

序列不支援事務，即使事務中進行了回滾(rollback)操作，序列仍然返回下一個元素。
序列不支援SQL Server 複製(replication)，序列不會複製到訂閱的SQL Server範例中。如果一個表的預設值依賴一個序列，而序列又是不可複製的，這會導致訂閱的SQL Server出現指令碼錯誤。

選擇使用序列的情況：

在以下情況下將使用序列，而非標識列：

應用程式要求在插入到表中之前有一個數值。
應用程式要求在多個表之間或者某個表內的多個列之間共用單個數值系列。
在達到指定的數值時，應用程式必須重新開始該數值系列。例如，在分配值 1 到 10 後，應用程式再次開始分配值 1 到 10。
應用程式要求序列值按其他欄位排序。 NEXT VALUE FOR 函數可以將 OVER 子句應用於該函數呼叫。 OVER 子句確保返回的值按照 OVER 子句的 ORDER BY 子句的順序生成。
應用程式要求同時分配多個數值。例如，應用程式需要保留五個序號。如果正在同時向其他程序發出數值，則請求標識值可能會導致在系列中出現間斷。呼叫 sp_sequence_get_range 可以一次檢索該序列中的若干數值。
您需要更改序列的規範，例如增量值。

二、建立序列：CREATE SEQUENCE

我們可以在SSMS中建立也可以使用SQL SERVER指令碼建立序列物件：

1、使用預設值建立序列：若要建立從 -2,147,483,648 到 2,147,483,647 且增量為 1 的整數序列號。

CREATE SEQUENCE Schema.SequenceName  
    AS int  
    INCREMENT BY 1 ;

2、若要建立類似於從 1 到 2,147,483,647 且增量為 1 的標識列的整數序列號，請使用以下語句。

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

3、使用所有引數建立序列

以下範例使用 decimal 資料型別（範圍為 0 到 255）建立一個名為 DecSeq 的序列。序列以 125 開始，每次生成數位時遞增 25。因為該序列設定為可迴圈，所以，當值超過最大值 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

執行以下語句可檢視第一個值；START WITH 選項為 125。將該語句再執行三次，以返回 150、175 和 200。再次執行該語句，以檢視起始值如何迴圈回到 MINVALUE選項值 100。

SELECT NEXT VALUE FOR Test.DecSeq;

1、序列值插入到表中

下面的範例建立一個名為 Test 的架構、一個名為 Orders 的表以及一個名為 CountBy1 的序列，然後使用 NEXT VALUE FOR 函數將行插入到該表中。

--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

下面是結果集：

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 子句為結果集生成序列號

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：同時獲取多個序列號

從序列物件中返回一系列序列值。序列物件生成和發出請求的值數目，併為應用程式提供與該系列序列值相關的後設資料。

以下語句從 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、將表從標識更改為序列

下面的範例建立一個包含該範例的三行的架構和表。然後，該範例新增一個新列並且刪除舊列。

使用 Transact-SQL 的 SELECT * 語句將這個新列作為最後一列接收，而非作為第一列接收。如果這樣做是不可接受的，則您必須建立全新的表，將資料移到該表中，然後針對這個新表重新建立許可權。

-- 新增沒有IDENTITY屬性的新列
ALTER TABLE Test.Department   
    ADD DepartmentIDNew smallint NULL  
GO  
  
-- 將值從舊列複製到新列  
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列中獲取當前的最高值，並建立一個用於列的序列。(返回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  
  
-- 檢視結果  
SELECT DepartmentID, Name, GroupName  FROM Test.Department ;   
-- Test insert  
INSERT Test.Department (Name, GroupName)  VALUES ('Audit', 'Quality Assurance') ;  
GO  
  
-- 檢視結果  
SELECT DepartmentID, Name, GroupName  FROM Test.Department ;  
GO

三、管理序列

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

重新開始 Samples.IDLabel 序列。

ALTER SEQUENCE Samples.IDLabel  RESTART WITH 1 ;

2、DROP SEQUENCE：刪除序列

在生成編號後，序列物件與其生成的編號之間沒有延續關係，因此可以刪除序列物件，即使生成的編號仍在使用。

當序列物件由儲存過程或觸發器參照時，可以刪除序列物件，因為序列物件未繫結到架構上。如果序列物件是作為表中的預設值參照的，則無法刪除序列物件。錯誤訊息將列出參照序列的物件。

以下範例從當前資料庫中刪除一個名為 CountBy1 的序列物件。

DROP SEQUENCE CountBy1 ;

3、檢視序列資訊

有關序列的資訊，請查詢 sys.sequences。

執行以下程式碼，以確認快取大小並檢視當前值。

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

到此這篇關於SQL Server序列SEQUENCE的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。