SQL Server设置自增的SEQUENCE,
有时候我们需要获取各式各样数据库自增的ID,但是SQLServer的自增列又不是所有都能满足需求,这时候想到自己书写一个自增的SEQUENCE
1:各种SEQUENCE存在一个表中
Name:SEQUENCE名称,seed:粒度,每次新增的间隔,value:目前的值
数据如下:
name | seed | value |
Order | 1 | 10 |
User | 1 | 9 |
2:重点来了,,,获取新的SEQUENCE的存储过程
ALTER PROCEDURE [dbo].[SP_GenSequenceNo]
-- Add the parameters for the stored procedure here
@name varchar(50),
@newId int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select @newId=a.value from System_Sequence a with(updlock) where a.name = @name
update System_Sequence set value+=seed where name=@name
END
3:调用存储过程测试;
DECLARE @RC int
DECLARE @name varchar(50)
DECLARE @newId int
set @name='Order';//这是你要获取的那个SEQUENCE的名称,
EXECUTE @RC = [dbo].[SP_GenSequenceNo]
@name
,@newId OUTPUT
select @newId;//返回的新的ID,每次会自增一个粒度
GO