SQL SERVER 2008 R2 实现 sequence

SQL SERVER 2008 R2 使用存储过程实现类似Oracle的sequence的功能：

-- Create the sequencing table that is used to hold the sequence values.

CREATE TABLE SQL_SERVER_SEQUENCE (
NAME VARCHAR(30) NOT NULL,
VALUE BIGINT DEFAULT 0 NOT NULL,
CONSTRAINT PK_SQL_SERVER_SEQUENCE PRIMARY KEY (NAME)
);
-- Create the stored procedure that is responsible for incrementing the sequence and return it.
ALTER PROCEDURE SQL_SERVER_SEQUENCE_NEXTVAL
@name VARCHAR(30)
AS
BEGIN
DECLARE @value BIGINT
BEGIN TRANSACTION
UPDATE SQL_SERVER_SEQUENCE
SET @value = value = value + 1
WHERE name = @name;
COMMIT TRANSACTION
SELECT @value AS NEXTVAL
END;

创建后，在表SQL_SERVER_SEQUENCE中添加一行

如：NAME    VALUE

         yypz        0

用法：

SQL_SERVER_SEQUENCE_NEXTVAL 'yypz'