May 1, 2008 at 10:24 am
I was reading a blog about bookmarks and one of the main things is if you try to return a row not covered by the non-clustered index
eg.
index covers (a,b)
select c
from t1
where b=1 and a=2
but if the index covered (a,b,c), it would in-sense, treat it like a clustered index and only read from the index and not from the page. And this would be most notably bad when you return many rows and cause many random page reads because of 'c' not being covered.
I'm assuming this wouldn't be an issue for a clustered index because the pages are inherently in-order, so random I/O won't be as much of an issue.
I'm also guessing that you don't want to cover too much stuff with an index, in that it becomes bloated and you mind as well read from the pages.
Thanks
May 1, 2008 at 10:28 am
Is there a question you have?
May 1, 2008 at 11:43 am
I don't quite see a question either.
BTW, having a covering index is not inherently a bad thing. Actually, it's inherently a good thing. But because of the cost of maintaining and storing indexes and because some data types won't index terribly well, you can't make a covering index for every eventuality.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply