在SQL Server中提供了 identity关键字,在创建表的时候指定,可以让某列实现自动增长。
而在SQL Server 2014中新增了序列Sequence,通过这个序列,不仅可以实现单表的某列的增长,还可以实现多表中的某列的自动增长。
下面是一个例子:
1、创建表、创建序列
drop sequence dbo.sequence_test
create sequence dbo.sequence_test
as int
start with 1
increment 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 int
set @n = next value for dbo.sequence_test;
insert into test(id,name,num)
values(@n,'b',20);
select next value for dbo.sequence_test --6
declare @n int
set @n = next value for dbo.sequence_test; --7
insert 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 test
select * from test1
4、在结果集中生成重复序列号
if object_id('sequence_tinyint') is not null
drop sequence sequence_tinyint
go
create sequence dbo.sequence_tinyint
as tinyint
start with 1
increment by 1
minvalue 1
maxvalue 2
cycle
go
--发现值是一样的
select next value for dbo.sequence_tinyint, --1
next value for dbo.sequence_tinyint
select next value for dbo.sequence_tinyint --2
select next value for dbo.sequence_tinyint --1
5、生成序列号
select *,
next value for dbo.sequence_test over(order by id) as 连续的id
from test
--注意:再次运行,值又发生变化了
select *,
next value for dbo.sequence_test over(order by id) as 连续的id
from test
6、重置序列号
alter sequence dbo.sequence_test
restart 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 ggg
add id_new int null
update ggg set id_new = id;
alter table ggg
drop constraint pk_ggg_id;
alter table ggg
drop column id
exec sp_rename 'dbo.ggg.id_new','id','column';
alter table ggg
alter column id int not null;
alter table ggg
add constraint pk_ggg_id primary key clustered(id);
alter table ggg
add constraint df_ggg_id default( next value for dbo.sequence_test) for id;
select next value for dbo.sequence_test
insert into ggg(name,id)
values('d',default)
select * from ggg