Chapter 17 — Build your own index

Erland Sommarskog is one smart dude.  I’ve referenced his site on numerous occasions throughout the years and I still reference his writing on dynamic sql.  In this chapter he takes on the challenge of making a search query with a preceding wild card run fast. 

Like many of the database folks who will read this, I have long wrinkled up my nose when I see this in a stored procedure ….  Where LastName LIKE ‘%Smith%’.   I grit my teeth when the product owner or the customer then asks me “why is it so slow”?  “It ran fast in our UAT environment with 482 records…”  etc…  You all know the conversation and you have likely had numerous conversations trying to explain why the database engine appears to be slower than pouring frozen molasses.

This chapter does not explain how to create a SQL Server index in the manner that we typically refer to them as; but rather it defines how to build your own index… literally.  It is not about the nuances of the Create Index statement. 

The content in this chapter is full of great t-sql including the usage of the MERGE statement, patindex, the OVER clause, CLR, TVF’s, the EXCEPT operator and CTE’s.  It would be worth going through the code provided by Erland in this chapter even if you don’t have a need to create your own index just so that you can see some great usage of newer t-sql enhancements that have been shipped with SQL Server 2005 and SQL Server 2008.

This chapter was a bit of a beast for me… I read it, re-read it.  Went through the code demo’s and then it clicked… and I mean clicked!  I’m so stoked about this that I have another idea on how to build my own index that I’ll be working on for the next couple of weeks…. If the effort is fruitful, I’ll be sure to blog about it and share what I have learned.

Erland, thank you for stretching my imagination, providing excellent code samples and donating your time to develop the solutions and write the chapter; all of which benefit War Child.