SQL SERVER2012 之前版本,一般采用GUID或者IDENTITY来作为标示符。在2012中,微软终于增加了 SEQUENCE 对象,功能和性能都有了很大的提高。
序列是一种用户定义的架构绑定对象,它根据创建该序列时采用的规范生成一组数值。 这组数值以定义的间隔按升序或降序生成,并且可根据要求循环(重复)。
bigint
。序列的限制(limitation)有二个
在以下情况下将使用序列,而非标识列:
我们可以在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 ;
执行以下语句可查看第一个值;START WITH
选项为 125。将该语句再执行三次,以返回 150、175 和 200。再次执行该语句,以查看起始值如何循环回到 MINVALUE
选项值 100。
SELECT NEXT VALUE FOR Test.DecSeq;
下面的示例创建一个名为 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
SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product WHERE Name LIKE "%nut%" ;
从序列对象中返回一系列序列值。 序列对象生成和发出请求的值数目,并为应用程序提供与该系列序列值相关的元数据。
以下语句从 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 ;
下面的示例创建一个包含该示例的三行的架构和表。 然后,该示例添加一个新列并且删除旧列。
使用 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
重新开始 Samples.IDLabel
序列。
ALTER SEQUENCE Samples.IDLabel RESTART WITH 1 ;
在生成编号后,序列对象与其生成的编号之间没有延续关系,因此可以删除序列对象,即使生成的编号仍在使用。
当序列对象由存储过程或触发器引用时,可以删除序列对象,因为序列对象未绑定到架构上。 如果序列对象是作为表中的默认值引用的,则无法删除序列对象。 错误消息将列出引用序列的对象。
以下示例从当前数据库中删除一个名为 CountBy1
的序列对象。
DROP SEQUENCE CountBy1 ;
有关序列的信息,请查询 sys.sequences。
执行以下代码,以确认缓存大小并查看当前值。
SELECT cache_size, current_value FROM sys.sequences WHERE name = "DecSeq" ;
到此这篇关于SQL Server序列SEQUENCE的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。
相关文章: