Grant Fritchey (2/15/2011)
Define a clustered index on the column that appears most frequently in the WHERE clause of SELECT
statements.
But not in JOIN criteria? No, no way. The basic rule for clusters should be the most frequently used access path to the data. This may be primary keys, this may be foreign keys, or it might be simply search criteria, but I wouldn't suggest limiting it to WHERE clauses.
Again, I'm not the "index Ninja" here, but I've also found that a table that suffers huge numbers of inserts can make very good use of a Clustered Index on an auto-numbering column such as an IDENTITY column or, perhaps, a date column to keep page splits to a reasonable level whether or not that column is the most frequently used in WHERE or JOIN criteria.
Have any of you good folks experienced the same or is there some other practice that folks use on a high insertion rate table?
--Jeff Moden
Change is inevitable... Change for the better is not.