Looking at an Index

One of the first Queries that I worked on as a DBA was how to search a field where a specific word appeared anywhere in the column that I wanted to search on. The column was a Name of every company in the United States. So my data looked like:

Joe’s Video Rental
Sue’s Oil Changes
Mom and Pop’s Grocery


It was very easy for me to understand that if I wrote a query that looked like this

Where name like ‘Joe%’

That I would get the results that I wanted, the wild card would allow for any number of any kind of values after the sting of ‘Joe’ was met. But what if I wanted to find all the places where I could get the Oil changed on my car? If I searched like this:

Where name like ‘Oil%’

My results would be empty, and that is not what I wanted. I wanted to know about Sue’s Oil Changes. For me to get the results that I wanted I would have to write my query to look like this:

Where name like ‘%Oil%’

The only issue with this query is that the index usage becomes a problem because indexes that would be sorted on the first letter in the first word would not include the word Oil. I have always called this a dual wild card search problem. And there is a couple ways to work around it. One would be to use Full Text Indexes, and there is a couple other not so elegant ways.

Erland Sommarskog has given a couple of options on how you can do this while keeping your performance. The best part is knowing that you have options to many of the problems that you may be faced with today as a DBA. That is what makes the book worth the price you pay. There are seasoned MVP’s who have worked out options, and what the impact of these options are. When was the last time in a SQL Server class do you remember being shown how to get around using wildcards on both sides of your searched value?

Every single chapter in this book is showing you the reader that you have options. Tips like this in the book make the whole book worth the cost if you only get one or two tips from it, but the truth is that every chapter has these tips. Enjoy reading the chapter.


0 Responses to “Looking at an Index”

  1. Leave a Comment

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 )

Google+ photo

You are commenting using your Google+ 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


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 11 other followers