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.
|
|
|
|
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 website, instead it is included a book that I collaborated on a few years back, and you will have to pay to get access to it. No, I am not greedy, here's the deal:. 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. For instance, there is an excellent introduction to full-text search by Robert C. Cain. Other chapters lead to you to higher levels such as Hugo Kornelis's chapter 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 from Greg Linwood how you can back up your database without backing up your indexes, and thereby save both time, disk space and energy.
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 other MVPs who contributed, and not the least Kalen, Greg, Adam, Paul and Kimberly who co-edited the book together with Paul. It was a fascinating project to have been part of.