SQL Server自动增量因另一个字段的值而异

2018-07-04 08:21:55

使用SQL Server 2008 R2我想有一个表（在2列上已经有一个主键），第三列是基于主键的两列之一的自动增量。

AIfield ，我想在向表中添加新记录时 ，自动增量文件AIfield自动递增，如下所示：

  PK1 PK2  AIfield 
  ------------------
  1   A     1
  1   B     2
  1   C     3
  2   A     1
  2   B1    2
  2   B2    3
  2   C1    4

其中PK1和PK2是主键的两个字段。

我不想使用明显的MAX(Afield)+1方法，因为很可能我必须为同一个PK1执行并发插入操作 - 迟早会在同一PK1中为AIfield创建重复项。

有什么建议么？

select pk1,pk2,ROW_NUMBER() over (partition by pk1,pk2 order by (select 0)) as AIfield
from yourtable

那么，一种方法可能是在PK1和AIfield上创建一个独特的索引

CREATE UNIQUE NONCLUSTERED INDEX [test] ON [Table] 
(
[AIfield] ASC,
[PK1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,  
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

并处理违反唯一性的行为

DECLARE @inserted BIT=0
DECLARE @AIfield BIGINT
SELECT @AIfield=MAX(AIfield)+1 FROM Table WHERE PK1=@PK1
WHILE @inserted=0
BEGIN
 BEGIN TRY
  INSERT INTO Table(AIfield,PK1,PK2)
  SELECT  @AIfield,@PK1,@PK2
  SET @inserted=1
 END TRY
 BEGIN CATCH
    IF ERROR_NUMBER()=2601
     BEGIN
     SET @AIfield=@AIfield+1
     END
    ELSE SET @inserted=1
 END CATCH
END

不过，我想知道是否有更多的SQL本地方法

使用以下查询：

Select PK1, PK2, ROW_NUMBER() over (partition by PK1 order by PK1, PK2) as AIfield
From yourtable

链接地址: http://www.djcxy.com/p/95653.html

上一篇: SQL Server AutoIncrement varying by value of another field

下一篇: SQL Server, How to set auto increment after creating a table without data loss?