SQL Server MVP Deep Dives

Chapter 17. Build your own index

Erland Sommarskog

Consider this SQL query:

SELECT person_id, first_name, last_name, birth_date, emailFROM   persons WHERE email LIKE '%' + @word + '%'

You can immediately tell that the only way the SQL Server can evaluate this query is to look through every single email address, be that by scanning the table or by scanning an index on the email column. If the table is large, say, ten million rows, you can expect an execution time of at least a minute.

Imagine now that there is a requirement that in most cases the response time for a search should be only a few seconds. How could you solve this? Regular indexes do not help, nor do full-text indexes; to be efficient both require that there is no leading wildcard ...