Krishna (2/20/2009)
Because in SQL server, the first column is enforced by an index
No, the entire primary key is enforced by an index, not just the first column.
A second index on the 1st column would be redundant. An index on the second (third, forth, etc) column of a primary key may be useful, depending on the types of queries that run.
I wrote a bit recently about index column order - http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Mainly Inserts, Transactional processing- could be deletes as well.
Updates can cause fragmentation as well, if the size of the row increases and the page is full.
A delete can't cause fragmentation, though it can leave pages partially empty.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability