SQL Indexes and substring searches

In the DB world, we often need to search for sub-strings. Let’s assume we have a very large table (MyTable) with millions of phone numbers (Number).
If we often need to lookup numbers, we’ll add an index on the Number field to speed up searches, now it’s time to build the queries:

We want to match area codes, so we code this simple SQL statement:

SELECT Number FROM MyTable WHERE LEFT(Number, 3) = '530'

Queries are sluggish, why? Well, SQL can’t use the index on Number since it’s wrapped around a function (LEFT) and who knows what this could return…
We then look at the query plan and see a table scan to confirm it all.

This is easily solved, by using a statement like this:

SELECT Number FROM MyTable WHERE Number like '530%'

We get the same results but we use the index.

This is a simple example, just to show the issue.
More often than not, you won’t have that easy fix.
You’ll need to match a sub-string, right of and so on.

In that case, you could (and maybe should) resort to de-normalizing and adding a computed column with the sub-string already parsed.
However, you need that column to be indexed and for most app I don’t think it’s worth going through the SET option requirements that this entails.
You could build your own “computed” column though, using triggers (yuck!), and index the result.

Whatever you do, make sure that you look at your query plan, don’t assume too much before you see it, and then make corrections.