SQL?Server序列SEQUENCE用法介紹
一、概述
SQL SERVER2012 之前版本,一般采用GUID或者IDENTITY來(lái)作為標(biāo)示符。在2012中,微軟終于增加了 SEQUENCE 對(duì)象,功能和性能都有了很大的提高。
序列是一種用戶定義的架構(gòu)綁定對(duì)象,它根據(jù)創(chuàng)建該序列時(shí)采用的規(guī)范生成一組數(shù)值。 這組數(shù)值以定義的間隔按升序或降序生成,并且可根據(jù)要求循環(huán)(重復(fù))。
- 序列不與表相關(guān)聯(lián),這一點(diǎn)與標(biāo)識(shí)列不同。
- 應(yīng)用程序?qū)⒁媚骋恍蛄袑?duì)象以便接收其下一個(gè)值。
- 序列是通過(guò)使用CREATE SEQUENCE語(yǔ)句獨(dú)立于表來(lái)創(chuàng)建的。 其選項(xiàng)使您可以控制增量、最大值和最小值、起始點(diǎn)、自動(dòng)重新開(kāi)始功能和緩存以便改進(jìn)性能。
- 與在插入行時(shí)生成的標(biāo)識(shí)列值不同,應(yīng)用程序可以通過(guò)調(diào)用NEXT VALUE FOR函數(shù)在插入行之前獲取下一序列號(hào)。 在調(diào)用 NEXT VALUE FOR 時(shí)分配該序列號(hào),即使在該序列號(hào)永遠(yuǎn)也不插入某個(gè)表中時(shí)也是如此。 此 NEXT VALUE FOR 函數(shù)可用作表定義中某個(gè)列的默認(rèn)值。
- 使用sp_sequence_get_range可一次獲取某個(gè)范圍的多個(gè)序列號(hào)。
- 序列可定義為任何整數(shù)數(shù)據(jù)類型。 如tinyint, smallint, int, bigint, decimal 或是小數(shù)精度為0的數(shù)值類型。如果未指定數(shù)據(jù)類型,則序列將默認(rèn)為
bigint
。
序列的限制(limitation)有二個(gè)
- 序列不支持事務(wù),即使事務(wù)中進(jìn)行了回滾(rollback)操作,序列仍然返回下一個(gè)元素。
- 序列不支持SQL Server 復(fù)制(replication),序列不會(huì)復(fù)制到訂閱的SQL Server實(shí)例中。如果一個(gè)表的默認(rèn)值依賴一個(gè)序列,而序列又是不可復(fù)制的,這會(huì)導(dǎo)致訂閱的SQL Server出現(xiàn)腳本錯(cuò)誤。
選擇使用序列的情況:
在以下情況下將使用序列,而非標(biāo)識(shí)列:
- 應(yīng)用程序要求在插入到表中之前有一個(gè)數(shù)值。
- 應(yīng)用程序要求在多個(gè)表之間或者某個(gè)表內(nèi)的多個(gè)列之間共享單個(gè)數(shù)值系列。
- 在達(dá)到指定的數(shù)值時(shí),應(yīng)用程序必須重新開(kāi)始該數(shù)值系列。 例如,在分配值 1 到 10 后,應(yīng)用程序再次開(kāi)始分配值 1 到 10。
- 應(yīng)用程序要求序列值按其他字段排序。 NEXT VALUE FOR 函數(shù)可以將 OVER 子句應(yīng)用于該函數(shù)調(diào)用。 OVER 子句確保返回的值按照 OVER 子句的 ORDER BY 子句的順序生成。
- 應(yīng)用程序要求同時(shí)分配多個(gè)數(shù)值。 例如,應(yīng)用程序需要保留五個(gè)序號(hào)。 如果正在同時(shí)向其他進(jìn)程發(fā)出數(shù)值,則請(qǐng)求標(biāo)識(shí)值可能會(huì)導(dǎo)致在系列中出現(xiàn)間斷。 調(diào)用 sp_sequence_get_range 可以一次檢索該序列中的若干數(shù)值。
- 您需要更改序列的規(guī)范,例如增量值。
二、創(chuàng)建序列:CREATE SEQUENCE
我們可以在SSMS中創(chuàng)建也可以使用SQL SERVER腳本創(chuàng)建序列對(duì)象:
1、使用默認(rèn)值創(chuàng)建序列:若要?jiǎng)?chuàng)建從 -2,147,483,648 到 2,147,483,647 且增量為 1 的整數(shù)序列號(hào)。
CREATE SEQUENCE Schema.SequenceName AS int INCREMENT BY 1 ;
2、若要?jiǎng)?chuàng)建類似于從 1 到 2,147,483,647 且增量為 1 的標(biāo)識(shí)列的整數(shù)序列號(hào),請(qǐng)使用以下語(yǔ)句。
CREATE SEQUENCE Schema.SequenceName AS int START WITH 1 INCREMENT BY 1 ;
3、使用所有參數(shù)創(chuàng)建序列
以下示例使用 decimal 數(shù)據(jù)類型(范圍為 0 到 255)創(chuàng)建一個(gè)名為 DecSeq 的序列 。 序列以 125 開(kāi)始,每次生成數(shù)字時(shí)遞增 25。 因?yàn)樵撔蛄信渲脼榭裳h(huán),所以,當(dāng)值超過(guò)最大值 200 時(shí),序列將從最小值 100 重新開(kāi)始。
CREATE SEQUENCE Test.DecSeq AS decimal(3,0) START WITH 125 INCREMENT BY 25 MINVALUE 100 MAXVALUE 200 CYCLE CACHE 3 ;
二、使用序列號(hào):NEXT VALUE FOR
執(zhí)行以下語(yǔ)句可查看第一個(gè)值;START WITH
選項(xiàng)為 125。將該語(yǔ)句再執(zhí)行三次,以返回 150、175 和 200。再次執(zhí)行該語(yǔ)句,以查看起始值如何循環(huán)回到MINVALUE
選項(xiàng)值 100。
SELECT NEXT VALUE FOR Test.DecSeq;
1、序列值插入到表中
下面的示例創(chuàng)建一個(gè)名為 Test 的架構(gòu)、一個(gè)名為 Orders 的表以及一個(gè)名為 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 語(yǔ)句中使用NEXT VALUE FOR。
SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;
3、通過(guò)使用 OVER 子句為結(jié)果集生成序列號(hào)
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:同時(shí)獲取多個(gè)序列號(hào)
從序列對(duì)象中返回一系列序列值。 序列對(duì)象生成和發(fā)出請(qǐng)求的值數(shù)目,并為應(yīng)用程序提供與該系列序列值相關(guān)的元數(shù)據(jù)。
以下語(yǔ)句從 RangeSeq 序列對(duì)象中獲取四個(gè)序列號(hào),并向用戶返回過(guò)程中的所有輸出值。
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)識(shí)更改為序列
下面的示例創(chuàng)建一個(gè)包含該示例的三行的架構(gòu)和表。 然后,該示例添加一個(gè)新列并且刪除舊列。
使用 Transact-SQL 的SELECT *
語(yǔ)句將這個(gè)新列作為最后一列接收,而非作為第一列接收。 如果這樣做是不可接受的,則您必須創(chuàng)建全新的表,將數(shù)據(jù)移到該表中,然后針對(duì)這個(gè)新表重新創(chuàng)建權(quán)限。
-- 添加沒(méi)有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)建一個(gè)用于列的序列。(返回3。) SELECT MAX(DepartmentID) FROM Test.Department ; --使用下一個(gè)期望值(4)作為START WITH VALUE; CREATE SEQUENCE Test.DeptSeq AS smallint START WITH 4 INCREMENT BY 1 ; GO -- 為DepartmentID列添加一個(gè)默認(rèn)值 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
重新開(kāi)始Samples.IDLabel
序列。
ALTER SEQUENCE Samples.IDLabel RESTART WITH 1 ;
2、DROP SEQUENCE:刪除序列
在生成編號(hào)后,序列對(duì)象與其生成的編號(hào)之間沒(méi)有延續(xù)關(guān)系,因此可以刪除序列對(duì)象,即使生成的編號(hào)仍在使用。
當(dāng)序列對(duì)象由存儲(chǔ)過(guò)程或觸發(fā)器引用時(shí),可以刪除序列對(duì)象,因?yàn)樾蛄袑?duì)象未綁定到架構(gòu)上。 如果序列對(duì)象是作為表中的默認(rèn)值引用的,則無(wú)法刪除序列對(duì)象。 錯(cuò)誤消息將列出引用序列的對(duì)象。
以下示例從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)名為CountBy1
的序列對(duì)象。
DROP SEQUENCE CountBy1 ;
3、查看序列信息
有關(guān)序列的信息,請(qǐng)查詢sys.sequences。
執(zhí)行以下代碼,以確認(rèn)緩存大小并查看當(dāng)前值。
SELECT cache_size, current_value FROM sys.sequences WHERE name = 'DecSeq' ;
到此這篇關(guān)于SQL Server序列SEQUENCE的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持本站。
版權(quán)聲明:本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái),僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。