September 17, 2012 at 2:44 pm
Eugene Elutin (9/17/2012)
jeffem (9/17/2012)
CELKO (9/14/2012)
By definition it cannot be a key; it is not an attribute of any entity in the logical data model. A well-designed key should have validation rules (a regular expression, check digit, etc) and verification (a trusted source, physical test, etc).Are you saying that every primary key must be based on a meaningful piece of data pertaining to the record? In essence, using an identity column is not proper?
Huh, you didn't know? Here you are: http://www.sqlservercentral.com/Forums/Topic1357081-392-1.aspx
You can start reading J.C. stand on this subject from page 1, but you need to read to the end to find out what he really offers instead...
Very interesting read.
I was curious of his take, because I take pride in my work of database design and sql development, so I don't enjoy being told I'm wrong on a solution I spent nearly a week on by somebody with no knowledge of my business. I was recently in a business situation where a meaningless PK was necessary. Our front-end architect was pushing for a GUID for certain reasons of his engine (which I still don't fully grasp as beneficial on his end), but I refused.
I went with an incrementing identity system, but instead of having an actual identity column (which I didn't want, for multiple reasons) or having a self-managed identity-type system that would require manual table-locking to avoid PK-assignment collisions, I actually generated 8.7 million incrementing values into a master table, with NULL values for the other 4 columns on the unassigned records. And then I have the IDs assigned to new records coming into our system, tens-of-thousands at a time. (I actually posted a thread on here requesting assistance on the best way to arbitrarily assign values as a set.)
Anyway, the GUID thing had me interested here, and then I saw JC's comment about the IDs and wanted some clarification. Thanks!
September 18, 2012 at 7:46 am
jeffem (9/17/2012)
Eugene Elutin (9/17/2012)
jeffem (9/17/2012)
CELKO (9/14/2012)
By definition it cannot be a key; it is not an attribute of any entity in the logical data model. A well-designed key should have validation rules (a regular expression, check digit, etc) and verification (a trusted source, physical test, etc).Are you saying that every primary key must be based on a meaningful piece of data pertaining to the record? In essence, using an identity column is not proper?
Huh, you didn't know? Here you are: http://www.sqlservercentral.com/Forums/Topic1357081-392-1.aspx
You can start reading J.C. stand on this subject from page 1, but you need to read to the end to find out what he really offers instead...
Very interesting read.
I was curious of his take, because I take pride in my work of database design and sql development, so I don't enjoy being told I'm wrong on a solution I spent nearly a week on by somebody with no knowledge of my business. I was recently in a business situation where a meaningless PK was necessary. Our front-end architect was pushing for a GUID for certain reasons of his engine (which I still don't fully grasp as beneficial on his end), but I refused.
I went with an incrementing identity system, but instead of having an actual identity column (which I didn't want, for multiple reasons) or having a self-managed identity-type system that would require manual table-locking to avoid PK-assignment collisions, I actually generated 8.7 million incrementing values into a master table, with NULL values for the other 4 columns on the unassigned records. And then I have the IDs assigned to new records coming into our system, tens-of-thousands at a time. (I actually posted a thread on here requesting assistance on the best way to arbitrarily assign values as a set.)
Anyway, the GUID thing had me interested here, and then I saw JC's comment about the IDs and wanted some clarification. Thanks!
1) By updating all those NULL fields to actual values, you are increasing the sizes of each row, which will lead to either fragmentation (if clustered index of any type) or a ton of forwarding pointers if a HEAP table. Possibly not an optimal solution.
2) You can handle set-based value-range acquisition from an identity-based driver table using OUTPUT clause and a table of numbers.
3) What are your "multiple reasons" for not using an identity column?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 18, 2012 at 9:43 am
...
3) What are your "multiple reasons" for not using an identity column?
...
I've worked once on similar system. All "multiple reasons" I have heard from original designers definitely didn't justify custom "identity generation". So I would also like to know which reasons made someone to do so...
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply