sql - 当 Id 为非自动编号时生成下一个 Id

Question

我有一个名为 Employee 的表。 EmpId 列用作主键。在我的场景中，我无法将其设为自动编号。

为要插入表中的新行生成下一个 EmpId 的最佳方法是什么？

我正在使用带有 C# 的 SQL Server 2008。

这是我目前得到的代码，但是要在键值对表或链接表(m*n 关系)中输入 Id

Create PROCEDURE [dbo].[mSP_GetNEXTID]
@NEXTID int out,
@TABLENAME varchar(100),
@UPDATE CHAR(1) = NULL

AS
BEGIN
    DECLARE @QUERY VARCHAR(500)

        BEGIN
        IF EXISTS (SELECT LASTID FROM LASTIDS WHERE TABLENAME = @TABLENAME and    active=1)
        BEGIN

            SELECT @NEXTID = LASTID  FROM LASTIDS WHERE TABLENAME = @TABLENAME and active=1

            IF(@UPDATE IS NULL OR @UPDATE = '')
            BEGIN
                UPDATE LASTIDS
                SET LASTID = LASTID + 1 
                WHERE TABLENAME = @TABLENAME
                and active=1
            END

        END
        ELSE
        BEGIN
            SET @NEXTID = 1

            INSERT INTO LASTIDS(LASTID,TABLENAME, ACTIVE)
            VALUES(@NEXTID+1,@TABLENAME, 1)
        END
    END

END

Answer 1

使用 MAX(id) + 1 在性能和并发方面都是一个坏主意。

您应该求助于 sequences专门针对此类问题设计的。

CREATE SEQUENCE EmpIdSeq AS bigint
  START WITH 1
  INCREMENT BY 1;

并生成下一个 id 使用:

SELECT NEXT VALUE FOR EmpIdSeq;

您可以在插入语句中使用生成的值:

INSERT Emp (EmpId, X, Y)
VALUES (NEXT VALUE FOR EmpIdSeq, 'x', 'y');

甚至将其用作您的专栏的默认设置:

CREATE TABLE Emp 
(
 EmpId bigint PRIMARY KEY CLUSTERED 
    DEFAULT (NEXT VALUE FOR EmpIdSeq),
 X nvarchar(255) NULL,
 Y nvarchar(255) NULL
);

---

更新:以上方案仅适用于SQL Server 2012+。对于旧版本，您可以使用带有标识字段的虚拟表来模拟序列行为:

CREATE TABLE EmpIdSeq (
 SeqID bigint IDENTITY PRIMARY KEY CLUSTERED
);

和模拟 NEXT VALUE 的过程:

CREATE PROCEDURE GetNewSeqVal_Emp
  @NewSeqVal bigint OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  INSERT EmpIdSeq DEFAULT VALUES

  SET @NewSeqVal = scope_identity()

  DELETE FROM EmpIdSeq WITH (READPAST)
END;

使用示例:

DECLARE @NewSeqVal bigint
EXEC GetNewSeqVal_Emp @NewSeqVal OUTPUT

删除最后插入的元素的性能开销将是最小的；尽管如此，正如原作者所指出的，您可以选择删除 delete 语句并安排维护作业以在下类时间删除表内容(性能交易空间)。

改编自SQL Server Customer Advisory Team Blog .

---

Working SQL Fiddle