Not directly related to the question asked by the OP... as with all else, I've found that "It Depends".:D
A lot of people do recommend that the clustered index should support as many queries as possible and, while that works, it doesn't always work well because of the width of the leaf level of the clustered index. For example, the system can only read whole pages even if a single row is to be queried. If the predominant method of data access is one row at a time and the table is wide with many columns that aren't actually used by most of the queries, then you're wasting a relatively large amount of memory on stuff that simply won't be used. The same is even more pronounced for large batch jobs and reporting queries.
There are other considerations, as well. Brent Ozar proposed that fragmentation doesn't matter because, once in memory, performance will be the same for well fragmented and totally unfragmented data and he is absolutely correct. I've proven that with test code. But, he was talking about "Logical Fragmentation" and he didn't emphasize the effect on "Read Aheads" if the data isn't already in memory and he didn't emphasize that the only way you can have logical fragmentation is if "Physical Fragmentation" in the form of page splits have occurred, which are devastating for non append-only inserts and expAnsive updates for duration, CPU, I/O, and the number of entries on the log file not to mention that it can easily waste a ton of memory by the "Natural Fill Factor" that results from such page splits which, in turn, can cause other queries to take longer because their data is driven out of memory.
To be sure, both Brent and Paul are correct (IMHO) but, as with all else in SQL Server, "It Depends".
My point is that folks can't just take the stance that your Clustered Index should be based on what satisfies the most queries because there's so much more that needs to be considered especially if the table is wide and long.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)