SQL Server中Sequence对象用法

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

一、Sequence简介

Sequence对象对于Oracle用户来说是最熟悉不过的数据库对象了，在SQL SERVER2012终于也可以看到这个对象了。Sequence是SQL Server2012推出的一个新特性。这个特性允许数据库级别的序列号在多表或多列之间共享。

二、Sequence基本概念

Oracle中有Sequence的功能，SQL server类似的功能要使用identity列实现，但是identity列有很大的局限性。微软终于在2012中添加了Sequence对象。与以往identity列不同的是：Sequence是一个与架构绑定的数据库级别的对象，而不是与具体的表的具体列所绑定。这就意味着Sequence带来多表之间共享序列号的便利之外，还会带来如下不利影响：

1、与identity列不同的是，Sequence插入表中的序列号可以被Update，除非通过触发器来进行保护
2、与identity列不同，Sequence有肯能插入重复值（Sequence可以设置循环，对于循环的Sequence来说会有重复值）
3、Sequence仅仅负责产生序列号，并不负责控制如何使用序列号，因此当生成一个序列号被Rollback之后，Sequence会继续生成下一个序列号，从而在序列号之间产生间隙

三、Sequence的用法

MSDN上对创建Sequence的语法如下：

CREATE SEQUENCE [schema_name . ] sequence_name  
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
    [ START WITH <constant> ]  
    [ INCREMENT BY <constant> ]  
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <constant> ] } | { NO CACHE } ]  
    [ ; ]

参数：

sequence_name
指定数据库中已知序列的唯一名称。类型是sysname。

[built_in_integer_type | user-defined_integer_type
可以将序列定义为任何整数类型。允许以下类型。

tinyint - 范围0到255
smallint - 范围-32,768至32,767
int - 范围-2,147,483,648至2,147,483,647
bigint - 范围-9,223,372,036,854,775,808至9,223,372,036,854,775,807
十进制和数字为0的规模。
基于允许类型之一的任何用户定义的数据类型（别名类型）。

如果没有提供数据类型，则使用bigint数据类型作为默认值。

START WITH <constant>
序列对象返回的第一个值。START值必须小于一个值，或等于最大且大于或等于所述序列对象的最小值。新的序列对象的默认起始值是升序序列对象的最小值和降序序列对象的最大值。

INCREMENT BY <constant>
用于每次调用NEXT VALUE FOR函数的序列对象的值递增（或减少为负）的值。如果增量是负值，则序列对象是递减的; 否则，它是升序。增量不能为0.新序列对象的默认增量为1。

[MINVALUE <constant> | NO MINVALUE ]
指定序列对象的边界。新序列对象的默认最小值是序列对象的数据类型的最小值。对于tinyint数据类型为零，所有其他数据类型为负数。

[MAXVALUE <constant> | NO MAXVALUE
指定序列对象的边界。新序列对象的默认最大值是序列对象的数据类型的最大值。

[CYCLE | NO CYCLE ]
指定序列对象是否应从最小值（或降序序列对象的最大值）重新启动的属性，或者在超过最小值或最大值时引发异常。新序列对象的默认循环选项为NO CYCLE。

请注意，循环从最小值或最大值重新启动，而不是从起始值开始。

[ CACHE [<constant>] | NO CACHE]
通过最小化生成序列号所需的磁盘IO数量，提高使用序列对象的应用程序的性能。默认为CACHE。

例如，如果选择缓存大小为50，则SQL Server不会保留缓存50个单独的值。它只缓存当前值和缓存中剩下的值的数量。这意味着存储缓存所需的内存量始终是序列对象的数据类型的两个实例。

1、创建一个简单的序列

CREATE sequence Seq_test --序列的名称：Seq_test
as bigint --类型
start with 100000001 --开始值
increment by 1 --步长
minvalue 1 --最小值
maxvalue 999999999 --最大值
no cycle --不循环
cache --设置缓冲

2、查询序列

创建了序列之后，可以通过SQL Server 2012新增的视图sys.sequences来查看刚才创建成功的Sequence，如下图所示：

--查看序列
SELECT * FROM sys.sequences WHERE name='Seq_test'

3、使用序列

在单表中插入序列

在多表间共享序列号

--创建测试表1和测试表2
 CREATE table #test1
 (
   id bigint
 )
  CREATE table #test2
 (
   id bigint
 )
 --插入测试数据
 DECLARE @index bigint
 SET @index=100000001
 WHILE (@index<100000005)
 begin
    insert INTO #test1(id) VALUES (NEXT value FOR Seq_test)
    insert INTO #test2(id) VALUES (NEXT value FOR Seq_test)
    SET @index=@index+1
 end
 --展示测试数据
 SELECT * FROM #test1
 SELECT * FROM #test2

结果如下图所示：

在可以看到，如果我们不指定Sequence的上限和下限，则默认使用所指定数据类型的最大值和最小值作为上限和下限（INT类型的的上下限）.当达到上线后，可以指定循环来让Sequence达到上限后从指定的开始值重新开始循环。

--创建序列
CREATE sequence Seq_test1 --序列的名称：Seq_test
as int --类型
start with 1 --开始值
increment by 1 --步长
minvalue 1 --最小值
maxvalue 5 --最大值
cycle --循环
--创建测试表
CREATE table test1
(
  id int
)
DECLARE @index int
SET @index=0
WHILE(@index<10)
begin
   insert INTO test1(id) VALUES (NEXT value FOR Seq_test1)
   SET @index=@index+1
end
--查看结果
SELECT * FROM test1

查询结果如下图所示：

可以通过修改Sequence将其初始值指定为一个特定值

--修改序列的值
ALTER sequence Seq_test1
restart WITH 3
--查询当前值
SELECT next value FOR Seq_test1

查询结果如下图所示：

Sequence一个需要注意的情况是Sequence只负责生成序列号，而不管序列号如何使用，如果事务不成功或回滚，SequenceNumber仍然会继续向后生成序列号

我们还可以为Sequence指定缓存选项，使得减少IO，比如，我们指定Cache选项为3，则当前的Sequence由1增长过3后，SQL Server会再分配3个空间变为从4到6，当分配到7时，SQL Server继续这以循环，如果不指定Cache值，则值由SQL Server进行分配。一个简单的例子如图所示。