Is there an efficient way to index inside words in SQL server 2008?

Question

We have a a few queries in our system that use LIKE '%'+@SomeCriteria+'%' to search a for a person's name. We are talking VARCHAR(50) fields in this case. We would really like to allow our users the ability to search within names.

The way I understand it, indexing the field will only make this faster if we search for the first part of the name, and full text indexing will search for specific words, but as far as I can tell doesn't help when searching within words.

Is there a good way in SQL 2008 to efficiently search inside of words for a given criteria without doing a complete table scan? Am I doomed to come up with a complex custom approach or have a consistently slow query?

Answer 1

This has been improved in SQL Server 2005, but it is not something you have a lot of direct control over...

http://technet.microsoft.com/en-us/library/cc966419.aspx#XSLTsection131121120120

Answer 2

What we did one time that helped with the slowness of the query was we had a stored proc that ran two pieces of code instead of one. First we did an exact match search and then only went to the inexact search if the exact one didn't return results (or in one case if the exact match search returned less than 5 results). That way, users who were willing to type the whole name got a fast response but those who only wanted to type a few characters got a slower one. You could do it with three possibilities as well, exact match, inexact match only on the end and inexact match at front and end.

Answer 3

Well, using Full Text Search would do the trick (EDIT: See comments below, this would NOT work for suffix/mid-term searches), but might be overkill for your scenario (or may not). Indexing will help very little as you already mentioned, though as @Aaron already mentions it is somewhat improved in 2k5 (only other way it would help is if you had a covered non-clustered index that would be scanned vs. the cluster, which would only help if the non-clustered index was actually smaller in raw size, i.e. less data to scan).

Possible custom solution would be to create a computed column from the character column you are searching and persist/index that, but that would really only be feasible/helpful if you were searching a set position or component of the string (for example, if you are be searching on something like email field, you could create a computed column that pattern-matches/materializes just the domain portion of the email address, then persist/index that and query there instead).

Answer 4

Back in the day I had a lot of success using Soundex. I had a requirement for Americans needing to search through similar sounding Eastern European last names, e.g. Elanov/Ilanov/Yllanov will have similar Soundex signatures.