Contents

1
2 谿瑚襭


[edit]

1 #

-- 襷り鍵
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
*/

[edit]

2 谿瑚襭 #