1 예제

2 참고자료

--시퀀스 만들기
if exists (select * from sys.sequences where name = 'seq1')
	drop sequence dbo.seq1;

create sequence dbo.seq1
as int
start with 1	--1부터 시작
increment by 1; --1씩 증가

--시퀀스 읽기. 이 경우는 1부터 읽어낸다. 
select next value for dbo.seq1 as nextval

--이 경우는 위에서 1이 읽혀졌으므로 2가 결과로 나온다. 
declare @nextval as int = next value for dbo.seq1;
select @nextval

--메타데이터
select * from sys.sequences where name = 'seq1'
select object_id('dbo.seq1', 'so')

select 
	current_value
,	start_value
,	increment
,	minimum_value --최소값을 지정할 수 있다.
,	maximum_value --최대값을 지정할 수 있다. 
,	is_cycling --순환되게 할 수 있다. 
,	is_cached --캐쉬에 담을 수 있다. (빠르겠지) 기본적으로 캐쉬된다.
,	cache_size --캐쉬사이즈도 지정
from sys.sequences 
where name = 'seq1';

--변경해보자. cycle
alter sequence dbo.seq1
restart with 1
cycle
maxvalue 3;

select next value for dbo.seq1 as nextval -- 1
select next value for dbo.seq1 as nextval -- 2
select next value for dbo.seq1 as nextval -- 3
select next value for dbo.seq1 as nextval -- -2147483648

alter sequence dbo.seq1
restart with 1
cycle
minvalue 1
maxvalue 3;

select next value for dbo.seq1 as nextval -- 1
select next value for dbo.seq1 as nextval -- 2
select next value for dbo.seq1 as nextval -- 3
select next value for dbo.seq1 as nextval -- 1

alter sequence dbo.seq1
restart with 1
no cycle -- 기본값이다.
minvalue 1
maxvalue 3;

select next value for dbo.seq1 as nextval -- 1
select next value for dbo.seq1 as nextval -- 2
select next value for dbo.seq1 as nextval -- 3
select next value for dbo.seq1 as nextval -- error
/*
Msg 11728, Level 16, State 1, Line 4
The sequence object 'seq1' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
*/

--테이블에 적용
if exists (select * from sys.sequences where name = 'seq1')
	drop sequence dbo.seq1;

create sequence dbo.seq1
as int
start with 1	--1부터 시작
increment by 1; --1씩 증가

create table dbo.t1
(
	seq int default(next value for dbo.seq1) primary key   
,	id varchar(50)
);

create table dbo.t2
(
	seq int primary key  
,	id varchar(50)
);

alter table dbo.t2
add constraint df_temp_seq
default (next value for dbo.seq1)
for seq;


create table #temp
(
	seq int default(next value for dbo.seq1) primary key  -- 안된다. db가 달라서 그런갑다. 
,	id varchar(50)
);

select next value for dbo.seq1
from (select 1 id) t

create table dbo.t3
(
	seq int primary key  
,	id varchar(50)
);
insert into dbo.t3 values(next value for dbo.seq1, 'aaa')
select * from dbo.t3


--next value for ... over()
--drop sequence dbo.seq
create sequence dbo.seq
as int
    start with 1
    increment by 1;

--drop table dbo.seq_test
create table dbo.seq_test
(
    seq int primary key
,   nm varchar(5)
);

insert dbo.seq_test
values
    (next value for dbo.seq, 'a')
,   (next value for dbo.seq, 'b')
,   (next value for dbo.seq, 'b')
,   (next value for dbo.seq, 'c')
,   (next value for dbo.seq, 'd')
,   (next value for dbo.seq, 'd');

select * from dbo.seq_test
/*
seq         nm
----------- -----
1           a
2           b
3           b
4           c
5           d
6           d
*/

select
    next value for dbo.seq over(order by nm) next_seq
,   nm
from dbo.seq_test;

/*
next_seq    nm
----------- -----
7           a
8           b
9           b
10          c
11          d
12          d
*/

• The Sequence Object in SQL Server 2012