SQL Server获取和设置SEQUENCE

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