Build Your Own Index!
(A Teaser)

An SQL text by Erland Sommarskog, SQL Server MVP.  Latest revision 2009-11-29.


Consider this SQL query:

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

You can immediately tell that the only way that SQL Server can evaluate this query is to look through every single email address, be that by scanning the table or by scanning any index there may be 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 the response time for a search should be just a few seconds in most cases. 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 in the search string.

There is one way out: you have to build your own index.

War Child logo

War Child logo
SQL Server MVP Deep Dives No Guns banner


So what does those images have to do with anything? And how do I build my own index?

No, I will not give you the solution here. You see, this article is not available on my web site, instead it is included a book, and you will have to pay to get access to it. No, I am not greedy, here's the dea:. The name of the book is SQL Server MVP Deep Dives and includes 59 texts from 53 SQL Server MVPs (and a few MVPs from other trades). All our royalties go to War Child International, a network of independent organisations, working across the world to help children affected by war. This is why I don't feel ashamed a slightest to ask you to buy this book.

Beside my chapter, you get a broad mix of content. Some articles gives you a basic introduction to some corner of SQL Server, others leads to you to higher levels, for instance how to combine cursors and set-based processing to tackle some problems where set-based processing alone fails to give good performance. And there is a completely breath-taking description of how you can back up your database without backing up your indexes, and thereby save both time, disk space and energy.

You can buy the book in many places, but I would really appreciate it if you can order it through, because this results in a referrer fee – that also goes to War Child.

I like to close this section with thanks to SQL Server MVP Paul Nielsen who took the initiative to this book and who encourage me and all the others to write a chapter. And I also like to thank Manning Publications Co for accepting to publish book. And, finally I like to thank all over MVPs who contributed, and not the least Kalen, Greg, Adam, Paul and Kimberly who co-edited the book together with Paul. It's a fascinating project to have been part of.

Back to my home page.