SQL SERVER – A Comparison of CHARINDEX vs LIKE

The use of functions in the WHERE clause of SQL queries can significantly impact performance. This is especially true when SQL Server must calculate the function for each row in the table. Therefore, using raw data in your WHERE clause is generally more efficient whenever possible. In this post, we’ll compare the use of CHARINDEX and LIKE in the WHERE clause, supported by real-world statistics.

When Functions Complicate Things

Consider a scenario where you need to fetch all persons whose last name starts with ‘D’ from the Person table in the AdventureWorks2019 database. You might think of using the CHARINDEX function or the LIKE keyword.

Here are the queries:

SET STATISTICS IO ON  
-- Query with Charindex
SELECT *
FROM AdventureWorks2019.Person.Person
WHERE CHARINDEX('D', LastName) = 1;
-- Query with Like
SELECT *
FROM AdventureWorks2019.Person.Person
WHERE LastName LIKE 'D%';

The first query uses the CHARINDEX function to find the position of ‘D’ in the LastName column and checks if it’s the first character. The second query uses the LIKE keyword to match any last names that start with ‘D’.

Real-World Statistics

We executed these queries and analyzed the statistics:

• CHARINDEX query:
  • Logical reads: 3880
  • Physical reads: 0
  • Read-ahead reads: 3864
• LIKE query:
  • Logical reads: 1717
  • Physical reads: 0
  • Read-ahead reads: 0

The LIKE condition clearly performs significantly fewer logical reads than the CHARINDEX function, demonstrating that avoiding using functions in the WHERE clause is more efficient whenever possible.

Here is the execution plan for the query where we can clearly see that the second query with Like is performing better than the query with CHARINDEX.

The Problem with CHARINDEX

Using CHARINDEX in the WHERE clause forces SQL Server to execute this function for every row in the table. If the table has many rows, this can be time-consuming and lead to a significant performance hit. Furthermore, it prevents SQL Server from effectively using indexes on the columns in the function, potentially leading to a full table scan.

The Efficiency of LIKE

In contrast, the LIKE keyword can be far more efficient, especially when used with an index on the LastName column. The LIKE 'D%' condition is sargable, meaning that SQL Server can effectively use an index to optimize the operation. It only needs to search the index tree for entries that start with ‘D’, which is significantly quicker than examining every row in the table.

Important Caveat

While LIKE provides a performance benefit over CHARINDEX for checking the first character, it’s important to note that this method won’t work if you’re checking for a character in any position other than the first. For example, if you need to find last names where ‘D’ appears as the second character, you would need to use CHARINDEX, as LIKE '_D%' wouldn’t work as expected.

Additionally, the performance difference is negligible if there is no index on the LastName column.

Conclusion

This comparison, backed by real-world data, demonstrates the significant impact that using functions in the WHERE clause can have on the performance of your SQL Server queries. By understanding the implications and knowing how to use more efficient alternatives where possible, you can optimize your queries and improve the overall efficiency of your SQL Server operations. Always consider the data type, collation, and indexing strategy when writing your SQL queries to ensure optimal performance.

You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)