computed columns, how to increment a subset

Question

What I want to do is have a table structure like this:

TypeID   int       not null (foreign key)
ItemID   int       (computed value +1 for each value with same TypeId) 
Data     string

e.g.

TypeId  ItemId  Data 
1       1       "some data"
1       2       "some data"
2       1       "some data"
3       1       "some data"
3       2       "some data"

If I have computed columns as a key, am I going to have issues with concurrency as opposed to identity columns?

Answer 1

The ItemId column seems to be the result of a ROW_NUMBER OVER(PARTITION BY TypeID) (see the docs) -- is that how you're computing it? As such it's not really suitable as part of a key due to concurrency issues (two transactions inserting rows with the same TypeID, as you mention). But what does that have to do with "how to increment a subset"? What subset of what? And what's your question? Why not just make something else the key instead (typically an artificial auto-increment column)?

Answer 2

I concur with Alex that your computed column could be reproduced with native analytic functionality (ROW_NUMBER, RANK, DENSE_RANK) rather than as a computed column, which likely would require using a CLR or just be better off with a trigger to populate the itemid column in the manner you desire.

I recommend having the itemid column as an identity column. What you want sounds like a surrogate key, a fake key (not to be mixed up with natural & artificial keys) that you can use to reference & display to users without giving away underlying data modelling. What I still don't like about the approach for your situation is that to use it, you still require two pieces of data to get a unique row/record - the typeid and the surrogate key.