When writing SQL queries, developers have a myriad of options at their disposal to filter and compare column values. Among these tools, the LIKE operator and the LEFT function are standard techniques used in string-matching scenarios. While they might return the same results, there are crucial differences in how they interact with indexes, affecting the overall performance of your queries. Let us learn to compare LIKE vs LEFT for Column Comparisons.
To illustrate this, let’s consider a scenario where we need to find all users in our database who live in ‘New York’ and have a name starting with ‘A.’ Here are two possible approaches to tackle this problem:
Approach 1: Using the LIKE operator
SELECT * FROM Users WHERE City = 'New York' AND Name LIKE 'A%';
Approach 2: Using the LEFT function
SELECT * FROM Users WHERE City = 'New York' AND LEFT(Name, 1) = 'A';
These statements are functionally identical – they return users residing in New York whose names begin with ‘A’. However, if you dig deeper, you’ll find they have significantly different performance characteristics.
Column Comparisons
The first query uses the LIKE operator with a wildcard ‘%’ at the end of the comparison string. Despite the presence of the wildcard, SQL Server can still leverage an index on the Name column, performing a range scan. This is much more efficient than scanning the entire table row by row.
On the other hand, the second query employs the LEFT function to extract the first letter of each name. Despite appearing more straightforward, using a function on a column prevents the SQL Server optimizer from fully utilizing indexes on that column. Applying a function to a queue before comparison doesn’t allow SQL Server to seek directly into the index. Instead, it leads to a full table scan, which can be significantly slower on large tables.
In my experience, running these queries on vast tables revealed that the first query (using LIKE) had significantly better performance – sometimes up to 100 times faster! This performance difference was confirmed when inspecting the execution plans, which showed an indexed seek in the case of the LIKE operator and a full table scan when using the LEFT function.
The key takeaway from this exploration is the importance of understanding how SQL functions and operators interact with indexes. Misuse or misunderstanding can lead to inefficient queries and performance bottlenecks. Tools like SQL Server Management Studio’s Execution Plan feature can be invaluable in providing insights into how your questions are executed under the hood. Performance tuning complex SQL queries often relies on understanding these underlying mechanisms, so don’t hesitate to use these tools to optimize your queries and improve your application’s performance. We discuss many such topics during the Comprehensive Database Performance Health Check.
You can follow me on X (twitter).
Reference: Pinal Dave (https://blog.sqlauthority.com)
