All I can really add to this is, yeah, me too. If you want some absolutely great advice on indexes, read this post. It’s a must.
And might I add, I’ve been the bad guy in Tim’s example. Once, many, many years ago, I was reading from the SQL Server 7.0 documentation. It suggested that compound indexes were no longer needed since the optimizer could build them on the fly using index intersection. I had a performance problem and a consultant was telling me to use a compound index. I swore up and down it wouldn’t work because Microsoft said so. He kept pushing and I kept pushing back. Finally, after a rather heated discussion in which I was convinced I had the upper hand, I got off the phone resolved to show this “ID 10 T” he didn’t know what he was talking about… Let me just say that after running some tests I did NOT enjoy the next phone call. Crow really tastes nasty.
Great post Tim. I’m looking forward to the book.
For what it’s worth, I think I’ve seen the optimiser using index intersections three times, maybe four. It’s rare enough to be memorable.
Saying that SQL can use index intersections is like that advice about ‘most selective column first’, true but leaving so much out as top make the advice nearly useless.