SQL Server序列SEQUENCE用法介紹
目錄
- 一、概述
- 選擇使用序列的情況：
- 二、創建序列：CREATE SEQUENCE
- 二、使用序列號：NEXT VALUE FOR
- 1、序列值插入到表中
- 2、在select 語句中使用 NEXT VALUE FOR 。
- 3、通過使用 OVER 子句為結果集生成序列號
- 4、sp_sequence_get_range：同時獲取多個序列號
- 5、將表從標識更改為序列
- 三、管理序列
- 1、更新（重置）序列：ALTER SEQUENCE
- 2、DROP SEQUENCE：刪除序列
- 3、查看序列信息
一、概述
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的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。
相關文章：
網公網安備