Re: Identity key?

In article <3A5B5C31.78AF_at_assist.org>,   larry_at_assist.org wrote:

Ah - at last the voice of pragmatism combined with good supporting arguments.

The is one of Joe's quoted disadvantages of identity columns that can be overcome:

>> 8) If you use IDENTITY as a key, the values tend to cluster on
 physical
>> data pages because they are sequential. The result is that if the
 most
>> recent rows are the most likely to be accessed, there will be locking
>> contention for control of those physical data pages. What you really
>> wanted in a key is some spread of the rows over physical storage to
>> avoid having every user trying to get to the same page at the same
>> time.
 

>Agreed. This is a big problem with identity values.

In SQL Server, specifically, this problem occurs only if the identity column is created as a clustered index. It can be worthwhile creating the natural key (or some other search column) as the clustered index and the identity key as a non-clustered index - this prevents the above problem on INSERT but can slow down retrieval where joins are made via the primary key. Where large number of INSERTS are required it is worth doing this, but where the volume of INSERTS is low then it is not. It just one of those trade-offs we have to make when designing.

--
Vaughan Powell MCDBA, MCSD, MCSE
Data Architecture and Applications Design Manager
BuildOnline


Sent via Deja.com
http://www.deja.com/