Tim Ford’s Top 5 Indexing Best Practices

April 27, 2009 at 8:31 am (SQL Server 2005, SQL Server 2008, TSQL) (, , )

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.

1 Comment

  1. Gail said,

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: