O'Reilly logo

SQL Server MVP Deep Dives by Greg Low, Paul Randal, Kimberly Tripp, Adam Machanic, Kalen Delaney, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required