Eirikur Eiriksson (8/29/2014)
ScottPletcher (8/29/2014)
patrickmcginnis59 10839 (8/29/2014)
CELKO (8/22/2014)
What do you use for the PK on lookup tables?
I use the encoding that is being used. The IDENTITY property (not a column!) is the count of insertion attempts that was made to that disk on that one machine.
Colums with the IDENTITY property can be per table, and you can have the same value for columns with the identity property in separate tables, so at best it would be the count of insertion attempts that were made to that TABLE.
If you want to be really technical, it's not even that. Some identity values can be discarded without a corresponding INSERT attempt, so it's not a true "count" of those. It's just an arbitrary identifier ... which is ok, because that's all that's needed!
Now, it's far too often used as a clustering key when it should not be, but it can still be useful as a pk or other unique identifier of a table row.
Just a quick question Scott, in your opinion which is better, a) using an identity column for the clustered index, guaranteeing an ever increasing order of values or b) unique numerical value which order of inserts cannot be guaranteed?
😎
It depends. How is the table queried? Is it a (very) large table? Is it joined to other (very) large table(s)? How are those other tables keyed? In some cases, it's worth adding a little freespace and/or having some small degree of fragmentation to make merge joins viable for (very) large tables (fragmentation is often vastly overestimated anyway: yes, there are multiple points of insert, but each of those is sequential as well).
If I key by ident, do I then have to create a gazillion covering indexes? That often causes far more overall I/O -- and wasted disk -- than just making the clustered index the main entry point for (almost) all queries.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.