How do I write a string search query that uses the non-clustered indexing I have in place on the field?

Question

I'm looking to build a query that will use the non-clustered indexing plan on a street address field that is built with a non-clustered index. The problem I'm having is that if I'm searching for a street address I will most likely be using the 'like' eval function. I'm thinking that using this function will cause a table scan instead of using the index. How would I go about writing one in this case? Is it just pointless to put a non-clustered index on an address3 field? Thanks in advance.

Answer 1

If your LIKE expression is doing a start-of-string search (Address LIKE 'Blah%'), I would expect the index to be used, most likely through an index seek.

If you search for Address LIKE '%Blah%', a table scan/index scan will occur, depending on how many fields you return in your query and how selective the index is.

Answer 2

varchar fields are indexed from left to right, much the same as a dictionary or encyclopedia is indexed.

If you knew what the field started with, (ex. LIKE 'streetname%') then the index would be efficient. However, if you only know part of the field (ex. LIKE '%something%') then an index cannot be used.

Answer 3

In theory the database will use whatever index is best. What database server are you using, what are you really trying to achieve, and what is your LIKE statement going to be like? For instance, where the wildcard characters are can make a difference to the query plan that is used.

Other possibilities depending on what you want to achieve are performing some pre-processing of the data and having other columns that are useful for your search, or using an indexed view.

Here's some discussion on the use of indexes with SQL Server 2005 and varchar fields.

Answer 4

Using LIKE will not necessarily use a table scan; it may make use of an index, depending on what string you're searching against. (For instance, LIKE 'something%' is generally able to use an index, whereas LIKE '%something' is probably not, although the server may still be able to at least do an index scan in that case, which is more expensive that a straight index lookup, but still cheaper than a full table scan.) There's a good article here that talks about LIKE vs. indexes with respect to SQL Server (different DBMSs will implement it differently, obviously).