Do you avoid avoid using wildcards at the start of a string?

Using a wildcard at the start of a string match can cause performance problems with queries. Find out more.

Video: Avoid wildcards at the start of filters for SQL performance | Bryden Oliver (4 min)

If you have a table that is indexed on a string column and then you use a query like:

SELECT * FROM Users WHERE Name LIKE '% Oliver'

Then have a think about whether the index can be used to efficiently to find the result. Unfortunately not, because the index is based on the first letter, then the second letter and so on of the column value. But in this case that's the bit that can be anything.

But there is a simple solution if this is a pattern that your application requires regularly. Basically create a new column (for instance ReverseName) and reverse the text in that column. Then you can write a query like

SELECT * FROM Users WHERE ReverseName LIKE Reverse('% Oliver')

As long as there is an index on the ReverseName column then suddenly this query can use the index to do a seek, dramatically reducing the amount of IO required by the query.