SQL Server AutoIncrement因另一个字段的值而异

问题描述

使用SQL Server 2008 R2，我希望有一个表(已经在2列上具有主键)，该表的第三列是基于主键两列之一的自动增量.

Using SQL Server 2008 R2 I'd like to have a table (already having a primary key on 2 columns) with a third column which is an autoincrement based on one of the two columns part of the primary key.

换句话说，我想在向表中添加新记录时，使一个自动递增文件AIfield如下自动递增:

In other terms, I would like when adding a new record to the table, have an autoincrement file AIfield automatically incremented as follows:

  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是主键的两个字段.

where PK1 and PK2 are the two fields of the primary key.

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

I do not want to use the obvious MAX(Afield)+1 approach, since it's very likely that I have to do concurrent inserts for the same PK1 - which would sooner or later create duplicates in AIfield for the same PK1.

有什么建议吗?

推荐答案

好吧，一种方法可能是在PK1和AIfield上创建唯一索引

Well, an approach could be creating a unique Index on PK1 and 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]

并处理违反唯一性的行为

and handling violations of uniqueness

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本机方法

I wonder if there is an approach more SQL native, though

这篇关于SQL Server AutoIncrement因另一个字段的值而异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！