Jeff Moden (12/1/2014)
Grant Fritchey (12/1/2014)
Just because it's not an incrementing number doesn't mean that it's not a good choice for the clustered index.
I have to admit that I'm taken aback by that statement a bit. Most would agree that "ever increasing" (which includes dates, which are really a pair of numbers behind the scenes, so long as you include another column that will make it unique along with being as narrow as possible and unchanging) is one of the prerequisites to a good clustered index. What else would you suggest?
And no... no irony intended here, ol' friend. I just can't think of anything else that would actually make a good choice on a monolithic table that suffers heavy inserts. If you know of some neat trick, I'd love to hear more about it.
No Jeff, no disagreement. That is the "best" possible clustered index. I've just found that you shouldn't let that aspect of the definition define your design. A unique, narrow, ever increasing key is the single best choice for a clustered index. But, what if we're in a situation where such a column just won't be used for the majority of the data access? I've seen the designs where there's a clustered primary key on an identity column, but then that column is never, ever referenced in any of the code (heck, I've done that in my youth). It's wasting the clustered index. Where, if you moved it to, just as an arbitrary example, to a name column, which is unique, but not terribly narrow at varchar(30), but is used in every single query, you suddenly receive all the benefits of the clustered index, even though it's not falling outside that "best" list.
I didn't intend to argue against what's best. I just don't think we should be bound by it as long as we're not doing things that are egregiously stupid (three column compound GUID keys and a clustered index on a bit column are two of my favorites).