Grant Fritchey (2/15/2011)

---

Jeff Moden (2/15/2011)

---

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?

I'm not an "index Niinja" either. I leave that to Gail, but in my experience and with my understanding, you're 100% right on here. Again, I like my own definition of "most frequently used access path" because that can apply to inserts as well as updates, deletes or selects. It doesn't matter. Since the cluster holds the data, you need to take advantage of that fact in whatever way is most advantageous to the system you're working on.