CREATE TABLE Test.dbo.Increments (

  Prefix varchar(50) NOT NULL,

  [MaxNum ] bigint NOT NULL,

  CONSTRAINT PK_Increments_Prefix PRIMARY KEY CLUSTERED (Prefix)

)

GO

--此处采用聚集索引主要考虑到大部分操作都是主键查找，叶子节点是数据的情况下会省1次IO　　

CREATE PROCEDURE dbo.SP_Seed_New

  @Prefix [nvarchar](32),

    @Qty [int] = 1

AS

BEGIN TRANSACTION

MERGE

INTO Increments WITH(HOLDLOCK) AS T  --事务结束释放，如果没有这个锁高并发情况下会存在主键冲突异常

USING (SELECT

    @Prefix AS Prefix

   ,@Qty AS Qty) AS S

ON t.Prefix = S.Prefix

WHEN MATCHED  --匹配时更新

  THEN UPDATE

    SET T.MaxNum = T.MaxNum + s.Qty

WHEN NOT MATCHED --不匹配时新增

  THEN INSERT (Prefix, MaxNum)

      VALUES (s.Prefix, s.Qty)

OUTPUT INSERTED.MaxNum AS MaxNum; --输出更新后的数字

COMMIT

GO