99.9% of you are never gonna need this.

But let’s say you need to run queries with leading (not trailing) wildcards, like this search for all the different national versions of the Encabulator, each of which has different prefixes depending on which government it’s being sold to:

We’ll simulate it with a last name search in Stack Overflow Users table – granted, you would never store names in a single column, but let’s pretend we’re doing that for the sake of this post:

Even if you’ve got an index on DisplayName, SQL Server can’t dive into the particular area of the index where the Ozars are stored, because there’s everybody from Avery Ozar to Zion Ozar, scattered all through the alphabet, and an index on DisplayName is stored in alphabetical order.

However, if you’re only searching for leading wildcards, you can create an index on the reverse of DisplayName, like this:

I can almost hear your record-scratch reaction from here. Let me explain.

I’m using an indexed view here because I don’t want to modify the underlying table. I could have used a computed column instead, but I’m trying to maximize the goofy number of things that I can show you in one blog post.

Next up in the list of oddball things I’m showing: putting the ReverseDisplayName in the unique clustered index for the view. Normally when you create a clustered index, you wanna follow the SUN-E principles that we discuss in the Mastering Index Tuning class. However, here, the only reason we’re creating this data structure is to make our search query faster. Think of this view’s clustered index as just a nonclustered index on the Users table itself.

Then, modify our query to search for the reverse of the DisplayName:

If you’re using Enterprise Edition and you’ve been living a good clean life, SQL Server will automatically recognize that the function you’re trying to run is already computed and indexed in the view. If you’re unlucky like me or if you’re using Standard Edition, you’ll have to modify your query to get SQL Server to read from the indexed view:

In either case, you’ll end up with a nice tidy index seek. The data’s organized backwards from the end of the string to the beginning, so SQL Server can dive into the razO% area of the index and read the rows out with just a few logical reads:

Isn’t that cool? Like I said, you’re probably not gonna need that – but if you do, it’s fun to know that techniques like this exist. Of course, it only works for leading wildcards, because if the search predicate has both leading and trailing wildcards, then we’re right back where we started.

Indexed views have their own gotchas – to learn more about those, watch this module of the Mastering Index Tuning class.