SQL Server AutoIncrement varying by value of another field
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.
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
where PK1 and PK2 are the two fields of the primary key.
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.
Any suggestions?
select pk1,pk2,ROW_NUMBER() over (partition by pk1,pk2 order by (select 0)) as AIfield
from yourtable
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
I wonder if there is an approach more SQL native, though
使用以下查询：
Select PK1, PK2, ROW_NUMBER() over (partition by PK1 order by PK1, PK2) as AIfield
From yourtable