【SQL 编程你也行】SQL Server 2014新功能之序列Sequence

来源：互联网发布：淘宝怎么入驻企业店铺编辑：程序博客网时间：2024/05/16 14:37

在SQL Server中提供了 identity关键字，在创建表的时候指定，可以让某列实现自动增长。

而在SQL Server 2014中新增了序列Sequence，通过这个序列，不仅可以实现单表的某列的增长，还可以实现多表中的某列的自动增长。

下面是一个例子：

1、创建表、创建序列

drop sequence dbo.sequence_testcreate sequence dbo.sequence_testas intstart with 1increment by 1;if object_id('test') is not null   drop table test;create table test (id int primary key,name varchar(20),num int);

insert into test(id,name,num)values(next value for dbo.sequence_test,'a',10);insert into test(id,name,num)values(next value for dbo.sequence_test,'b',20),      (next value for dbo.sequence_test,'c',30),      (next value for dbo.sequence_test,'d',40);select * from test

2、使用序列

declare @n intset @n = next value for dbo.sequence_test;insert into test(id,name,num)values(@n,'b',20);select  next value for dbo.sequence_test --6declare @n intset @n = next value for dbo.sequence_test;  --7insert into test(id,name,num)values(@n,'b',20);select * from test

3、在多个表使用序列

if object_id('test1') is not null   drop table test1;create table test1 (id int primary key,name varchar(20),num int);insert into test1(id,name,num)values(next value for dbo.sequence_test,'a',10);insert into test1(id,name,num)values(next value for dbo.sequence_test,'111111111111111111111111111111111',10);  --由于超出了定义长度,所以报错,但序列值会继续递增到下一个insert into test1(id,name,num)values(next value for dbo.sequence_test,'a',10);select * from testselect * from test1

4、在结果集中生成重复序列号

if object_id('sequence_tinyint') is not null   drop sequence sequence_tinyintgocreate sequence dbo.sequence_tinyintas tinyintstart with 1increment by 1minvalue 1maxvalue 2cyclego--发现值是一样的select next value for dbo.sequence_tinyint, --1       next value for dbo.sequence_tinyintselect next value for dbo.sequence_tinyint   --2select next value for dbo.sequence_tinyint   --1

5、生成序列号

select *,       next value for dbo.sequence_test over(order by id) as 连续的idfrom test--注意:再次运行,值又发生变化了select *,       next value for dbo.sequence_test over(order by id) as 连续的idfrom test

6、重置序列号

alter sequence dbo.sequence_testrestart with 1;--有从1开始select next value for dbo.sequence_test;

7、将表从标识更改为序列

if object_id('ggg') is not null   drop table ggg;create table ggg(id int identity(1,1) not null,name varchar(10)constraint pk_ggg_id primary key clustered (id));insert into ggg(name)values('a'),      ('b'),      ('c');      alter table gggadd id_new int nullupdate ggg set id_new = id;alter table gggdrop constraint pk_ggg_id;alter table gggdrop column idexec sp_rename 'dbo.ggg.id_new','id','column';alter table gggalter column id int not null;alter table gggadd constraint pk_ggg_id primary key clustered(id);alter table gggadd constraint df_ggg_id default( next value for dbo.sequence_test) for id;select next value for dbo.sequence_testinsert into ggg(name,id)values('d',default)select * from ggg

0 0