SQL Server：Sequence Objects初体验-技术开发专区

SQL Server：Sequence Objects初体验

作者：王立国编辑：王玉圆 2012-12-12 00:05 IT168网站原创

【IT168 技术】SQL Server 2012可以说是集众家之所长，很大程度的丰富了自身的内容和功能，对T-SQL进行了全面的扩展增强。例如新增字符函数CONCAT ,用法和MYSQL中的CONCAT 函数一致;新增了内置逻辑函数IIF ,例如VB，Microsoft Office Access等等里面都是有IIF函数的;新增了类似MYSQL 中的limit n,m的形式来读取第n行到第m行的数据，有了使用OFFSET/FETCH NEXT分页的功能;新增了Oracle中存在的分析函数FIRST_VALUE() 和 LAST_VALUE();新增了Sequence，用过Oracle的朋友一定对序列的概念非常的熟悉，原本SQL Server 中是有identity自增列来完成相关功能的，那为什么还要引用Sequence Objects?

　　在SQL Server 2012中怎么来创建序列?又怎么样来使用序列?本文我们就来探讨一下。

　　一. Sequence Objects的创建

　　1.1 可视化向导创建序列

　　我新建了一个空的数据库2012SeqTest，找到数据库-2012SeqTest-可编程性-序列，选中序列右键，在弹出的右键菜单中点击“新序列”。

▲图A

　　然后我们可以看到如下界面：

▲图B

　　从上图B中，我们可以看到Sequence中有最小值，最大值，循环和缓存选项设置，这是都是自增列所没有的，例如这个循环和缓存选项是干什么用的，我们在后文中会有相关介绍，我们接下来介绍一下如何用T-SQL来创建Sequence。

　　1.2 用T-SQL创建序列

    create sequence SqlSeq
    as bigint --数据类型
    start with 1  --开始值
    increment by 1 --增量
    minvalue 1  --最小值
    maxvalue 100 --最大值
    no cycle --不循环
    cache 3  --设置cache大小为3

　　上面的T-SQL可以创建一个名为SqlSeq的序列，创建是成功的，这么这个序列是不是有问题呢?一个1到100的序列我使用了bigint类型是不是一种浪费呢?最小值是1，最大值是100，不循环，这个序列是不是很快就不能用了呢?我们下文会对上面的两个序列进行实例分析。

　　1.3 查看创建的序列

▲图C

　　创建以后的序列，我们如何用T-SQL来查看呢?

    select
        name,object_id,type,
        start_value,increment,minimum_value,maximum_value,
        is_cycling,is_cached,cache_size,current_value
    from sys.sequences

▲图D

　　这里我们可以发现这个cached选项设置默认大小的时候，sys.sequences里的cache_size属性是null。

　　我们还可以看出sequence的type类型是’SO’，应该是Sequence Objects的缩写。

　　就是说我们也可以这样查看存在的序列：

　　select * from sysobjects where type='SO'

${PageNumber}

　　二. Sequence Objects的使用

　　下面我们来看看这个序列是怎么来用的。

    --创建一个表
    Create table Students(ID int,studentID varchar(20), studentName nvarchar(20))
    --插入数据
    insert into Students
    values(next value for MacoSeq,'A001','张三')
    insert into Students
    values(next value for MacoSeq,'A002','李四')
    insert into Students
    values(next value for MacoSeq,'A003','王五')
    select * from Students
    /*
    ID          studentID            studentName
    ----------- -------------------- --------------------
    1           A001                 张三
    2           A002                 李四
    3           A003                 王五
    */

　　我们可以单独执行下面的语句select next value for MacoSeq。

　　例如：

    select next value for MacoSeq
    go 20
    insert into Students
    values(next value for MacoSeq,'A003','陈七')
    /*
    ID          studentID            studentName
    ----------- -------------------- --------------------
    1           A001                 张三
    2           A002                 李四
    3           A003                 王五
    24          A003                 陈七
    */

　　三. Sequence Objects的修改

　　例如下面的序列已经到了24，如何让它重新从3开始呢。

    ALTER SEQUENCE MacoSeq
    RESTART WITH 3
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 100
    NO CYCLE
    NO CACHE

　　上面的代码可以对刚才的MacoSeq序列进行修改，重新设置它的属性值。

　　四. Sequence Objects的优势

　　如果提到Sequence的优点，那一定要和identity自增列对比着来说。

　　4.1 可以多表共享

　　既然Sequence Objects是独立的对象，那么它就可以在多个表中使用，使不同的表实现连续的序号。

　　4.2 可以很方便的设置最大值，最小值

　　SQL Server中原来的自增列是靠数据类型的上下限来约束的。

Sequence Objects的使用

▲图E

　　MacoSeq从1-100的不循环的序列，我们来看一下如果溢出，数据库会给出怎么样的提示：

▲图F

　　4.3 可以重复

　　Sequence Objects可以设置CYCLE属性，让序列自动可以再次从头开始。我们修改SqlSeq序列为1-4的可循环的序列：

    ALTER SEQUENCE SqlSeq
    RESTART WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 4
    CYCLE
    NO CACHE

　　多次执行后的结果：

▲图G

　　4.4 可以更新

　　使用自增列后该行数据无法更新，使用序列是只是得到插入的数据，并不对列产生任何的约束或是其他影响。

　　4.5 CACHE选项可以提高性能

　　通过最大限度地减少生成序列编号所需的磁盘 IO 数，可以提高使用序列对象的应用程序的性能。

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

　　为了提高性能，SQL Server 会预分配 CACHE 参数所指定数量的序列号。

　　例如，用起始值1和缓存大小15创建一个新序列。在需要第一个值时，便可从内存中使用值1到15。最后缓存的值 (15) 将写入磁盘上的系统表中。使用完所有的15个数字后，下一个请求(数字16)将导致重新分配缓存。新的最后缓存值 (30)将写入系统表中。

　　我觉得我们可以这样来理解：这个CACHE的大小相当于网页缓存的天数。

　　综合来看，无论是高效性，还是灵活性，Sequence Objects都比自增列要强很多。相信Sequence Objects一定会给我们带来新的体验。

关注我们