Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

Searching a query result from the table with LIKE operator with string in %% always takes a huge time.

This happens because, A LIKE with a leading % cannot seek on an index because the index is ordered on the basis of the leading characters. If it has to look for characters in the middle of the string then it cannot use the index to seek for rows. (that doesn’t mean it won’t scan the index because it is smaller than the table itself – so there may still be performance improvements on this basis).

Example:-

If user ,app or code is looking for username with example “dhar” in the middle of string value.it will search like ‘%dhar%’.

Fixes (Subject to testing)

We can use charindex clause in WHERE clause and make sure the WHERE clause column should have index created on it. and code condition can be modified like below (pls test, validate before making any changes to the code and deploying it to the prod)

SELECT column
FROM tableName 
WHERE charindex(@SearchTerm, column1) > 0 OR charindex(@SearchTerm, column2) > 0

Happy Learning!