Presents your 
SQL SERVER E-NEWSLETTER for October 10, 2002

<------------------------------------------->

Avoid IDENTITY Primary keys as clustered Indexes

Having a primary key that has the identity property and a clustered
index will cause hot spots when a table runs out of room and a new page 
is created for additional room. Hot spots are created where multiple
transactions are inserting on the same table and are competing for the 
same resource or new page that has just been allocated.

When inserting in a table with incremental identity values, the same
page is allocating entries for each insert on the same page, causing a
bottleneck with multiple transactions. The primary purpose of a 
clustered index is to spread the data across pages as evenly as possible, 
reducing the contention for the same resources especially for inserts and updates.
This is a possible scenario for an online transaction processing (OLTP)
environment.

However, in a reporting database, clustered indexes can be used for
primary and foreign key indexes, improving performance of joins by 
increasing the potential of merge joins. In this situation, the primary key may
have the identity property.

In a reporting database, the volume in tables should be quite large. 
The reporting database should use a batch inserts utility to insert this
data, thereby foregoing the contention problems that are seen in an 
OLTP database. If the primary key of one table with a large amount of rows 
has the identity property and a foreign key of another table that 
references the primary key of the large table, both the primary key and foreign 
key are candidates for clustered indexes.

Another factor for considering clustered indexes on primary and foreign
key relationships would be the frequency of queries accessing this join
condition between the primary and foreign keys. If the frequency of 
this join is high, then there is even more reason to use a clustered index 
on the primary and foreign key columns.

----------------------------------------