Suppose, I have a composite primary key that consists of 3 columns: [ShardKey], [SiteId] and [ServiceId]. Column [ServiceId] is an identity column and should start from 1 for a new combination of [ShardKey] and [SiteId], but SQL server fails – the column [ServiceId] never starts from 1, it just increments its value no matter what the primary key is. I did add constraint for primary key that holds all three columns. What should I do to make it start from 1 when a new combination of [ShardKey] and [SiteId] comes in?
[ShardKey] [SiteId] [ServiceId] [Name] 1009 1 1 Coffee 1009 1 2 Tea 1009 1 3 Cocaine 1009 2 1 Coffee 1009 2 2 Tea 1009 2 3 Cocaine 1010 1 1 Coffee 1010 1 2 Tea 1010 1 3 Cocaine
That’s what I want SQL server to do for me. Values for [ShardKey] and [SiteId] are known to me, so I can always insert them into SQL script, but I need the column [ServiceId] to start from 1 for a new combination of [ShardKey] and [SiteId]
Advertisement
Answer
The short answer is “no, IDENTITY can’t help you here” – IDENTITY is per-table, not per-unique-other-primary-key-components. So: you’d have to handle this yourself (presumably checking the maximum ServiceId for that permutation), but then you get into complex race condition scenarios. It also makes insert very complex and less efficient, as now you’re doing a select immediately before the insert – a prime candidate for deadlocking in some isolation levels (so: that initial select would need to be UPDLOCK). The fact that the primary key will be unique will help avoid some of the more obvious problems, but you could still get into problems if you make assumptions about the value you’re about to insert before you’ve fully inserted it, or if you don’t explicitly expect the insert to fail.