November 26, 2014 at 8:45 am
Hi folks,
lets say i have a row which at the moment has an autoincrement as pk. Now i need several copies of this row in my table. This forces me to kill the autoincrement-pk and to use a combined pk consisting of 3 integer-fields or to use a guid. What would be the best way to do this? What would you do?
Greetz
Query Shepherd
November 26, 2014 at 10:37 am
Well, first of all, I'd say that while an identity column does uniquely identify a row, you still should have the business key as a unique constraint on the table. Auto-incrementing values shouldn't be the business key (which it sounds like you might be doing). That way, you never have to worry about suddenly having to duplicate the values of an identity column (which you shouldn't ever do).
The question for me is, what's the right clustered index on the table? Will most of the data access be through this single field, or through the compound of three integer columns? If the former, keep that as the cluster, but add a unique constraint to satisfy the appropriate structures. If the latter, drop the identity column and go with the three columns as your key.
Where possible, I try to avoid GUIDs, although I don't have the phobia about them that most do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2014 at 2:38 pm
Hi Query Sheperd,
Grant makes some good points. If his response answered your question then read no further.
For my two cents, I would find it helpful to know a bit more about what your data looks like and what it is that you're data is trying to do by "duplicating" rows (but still maintaining uniqueness). You say it's necessary to have multiple copies of the same row in the table, but that alternatively, you could do that and maintain uniqueness by using three other columns as an alternate key. It would seem to me that if you have such a natural key, your problem can be solved by using those three columns in a unique index, and then just insert the "duplicate" rows. If you end up with a new auto-incrementing number, that's fine, because the real constraint is maintained by virtue those three columns, not the identity column.
November 27, 2014 at 4:46 am
Ok. I knew my thought was a design faux pas and not common practise, so lets say i keep the original pk.
But now I won't to use a second "key" of 3 values, maybe as unique index, in the database. Would it be better to use 3 int-fields or a bigint-field with a self-generated guid-key concerning indexing. What about index fragmentation if an index is not clustered and not incremental? Then the index is not sorted, right? And hence I got no fragementation problems, right?
Greetz
Query Shepherd
November 27, 2014 at 5:21 am
The GUID will fragment a lot more, that's for certain. I'd be inclined to use the three integers. They sound like the natural key anyway. And compound keys are not bad. Yes, the guidelines are to have the index key as small as possible, but it is just a guideline.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2014 at 8:32 am
Primary and Unique are not necessarily the same thing 🙂
Where the unique key is a compound key (e.g. if it were a flight booking system then Flight#,date,seat#) then I would almost always add a separate autoincrementing ID field. This is because I can then use that ID as the Foreign Key (for example, linking the passenger to the seat). The bridge table is easier to manage when it is SeatID/CustomerID rather than (Flight#/date/seat#/surname/firstname/dob)
If you are going to use compound keys as FKs then you must ensure that they are immutable. Names can change, passport numbers - not so often...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply