Utilizing Indexes When Search Text Begins with a Wildcard

Searching for a value or group of values with a wildcard is more than just putting a % on both sides of a text string. If you know you’re looking for all strings in a name field that start with the name “Chad” then you are you really shooting yourself in the foot by using ‘%Chad%’ instead of ‘Chad%’ in your query. SQL Server is going to be scanning the table instead of being able to use an index to seek to the data. While that may work to get your result, it’s likely going to take longer and be more invasive than needed. I want to go through an example of how using a reversed column can improve SQL Server’s ability to build a better execution plan.

Reverse Psychology

We’ll start by creating a new table in the Stackoverflow2013 database to test with:

CREATE TABLE UsersTest 
(
	DisplayName NVARCHAR(40)
	,Location NVARCHAR(100)
	,ReverseLocation AS REVERSE(Location)
);
GO

Note the ReverseLocation column. It will be populated based on the Location value but reversed with the REVERSE function. If there is a Location value of “Ohio” then “oihO” will be the ReverseLocation value.

Next, we’ll create a couple indexes that we’ll use later:

CREATE INDEX IX_Location ON UsersTest (Location) 
INCLUDE (DisplayName);
GO

CREATE INDEX IX_Location_Reverse ON UsersTest (ReverseLocation,Location) 
INCLUDE (DisplayName);
GO

Now, we’re ready to populate our new table based on Users table data:

INSERT INTO UsersTest
SELECT DisplayName, Location
FROM Users;
GO

OH-IO

For our first example, we’ll say we want to query for Users where the Location begins with Ohio. When running the query below, we’ll see that our IX_Location index gets used and we can seek to the data:

SELECT DisplayName, Location FROM UsersTest 
WHERE Location LIKE 'Ohio%';
GO

This query is sargable aka SQL Server was able to use an available index (IX_Location) to retrieve data. If we take our wildcard character and place it at the beginning of our search, we’ll get a different plan:

SELECT DisplayName, Location FROM UsersTest 
WHERE Location LIKE '%Ohio';
GO

SQL Server will have to scan the entire table. Remember the reversed column we created with our test table? What if we use that to do our Ohio search:

SELECT DisplayName,Location
FROM UsersTest
WHERE ReverseLocation LIKE REVERSE('Ohio') + '%';
GO

We got our index seek! If you don’t like the looks of writing the query that way, you can also run the following query without referencing the ReverseLocation column at all but still use the IX_Location_Reverse index:

SELECT DisplayName, Location FROM UsersTest 
WHERE Reverse(Location) LIKE 'oihO%';
GO

Digging into the query even more, we had the following logical reads when using the index:

If we drop the IX_Location_Reverse index and run the same query again, we see about 1000x as many logical reads:

Is it worth it to add a reverse column for every type of string column? Not at all. Still, this type of addition is worth keeping in mind because it can make a difference when running into query issues with wildcards.

Thanks for reading!