先创建一个序列表

if exists (select 1

from sysindexes
where id = object_id('TSysSequence')
and name = 'In_SeName'
and indid > 0
and indid < 255)
drop index TSysSequence.In_SeName
go

if exists (select 1
from sysobjects
where id = object_id('TSysSequence')
and type = 'U')
drop table TSysSequence
go

/*==============================================================*/
/* Table: TSysSequence */
/*==============================================================*/
create table TSysSequence (
SeName nvarchar(50) not null,
Increment int not null default 1,
CurVal bigint not null default 0
)
go

if exists (select 1 from sys.extended_properties
where major_id = object_id('TSysSequence') and minor_id = 0)
begin
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description',
'user', @CurrentUser, 'table', 'TSysSequence'

end

select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'
模拟oracle序列

不允许用户维护，数据库初始化以后不允许任何人修改其中的值。

默认生成名称为“DID”和“SID”的两个序列，意义为“数据序列号”和“系统序列号”。',
'user', @CurrentUser, 'table', 'TSysSequence'
go

insert into TSysSequence (SeName,Increment,CurVal) values ('DID',1,0) ;
insert into TSysSequence (SeName,Increment,CurVal) values ('SID',1,0) ;

/*==============================================================*/
/* Index: In_SeName */
/*==============================================================*/
create unique index In_SeName on TSysSequence (
SeName ASC
)

go

再创建一个存储过程完成序列的使用

if exists (select 1
from sysobjects
where id = object_id('PGetSequenceValue')
and type in ('P','PC'))
drop procedure PGetSequenceValue
go

create procedure PGetSequenceValue
@SeName nvarchar(50),
@SeVal bigint out
as
begin
if not exists(select 1 from TSysSequence where SeName = @SeName)
begin
raiserror('不存在序列%s',16,1,@SeName)
return
end

update TSysSequence set @SeVal = CurVal + Increment, CurVal = CurVal + Increment where SeName = @SeName

end
go

使用方法

declare @ID1 int

EXEC PGetSequenceValue 'SID',@ID1 OUTPUT

select @ID1

declare @ID2 int

EXEC PGetSequenceValue 'DID',@ID2 OUTPUT

select @ID2