The Intricacies of T-SQL String Comparison: LIKE VS ‘=’

In SQL, the way you compare strings can greatly impact the results of your queries. Beyond this, the choice between different comparison methods can also influence the performance of your queries. Let’s delve into this topic by examining two T-SQL queries that, while seemingly similar, yield different results due to their distinct string comparison methods, and discuss how they interact with indexes for performance tuning. Let us learn The Intricacies of T-SQL String Comparison.

This blog post is based on the tweet here:

Assuming there is an Index on the FirstName Column, will the following queries give Index Seek, Index Scan, or something else? #SQLServer pic.twitter.com/0C6jPWLpNm

— Pinal Dave (@pinaldave) October 3, 2023

The Queries

Consider the following two queries:

Query 1:

SELECT *
FROM Employees
WHERE FirstName LIKE 'John%';

Query 2:

SELECT *
FROM Employees
WHERE FirstName = 'John';

Both queries aim to return employees whose first name is ‘John’. However, because they employ different comparison methods, they will provide different results. I hope you find this Intricacies interesting.

Understanding the Difference

Query 1: LIKE Operator

The first query uses the LIKE operator paired with a wildcard character (%). This operator matches any string that begins with ‘John’, thus returning rows where FirstName is ‘John’, ‘Johnathan’, ‘Johnny’, and so on. The LIKE operator with a wildcard character offers flexibility by matching a range of similar names.

Query 2: = Operator

The second query, in contrast, uses the = operator, which matches the exact string. This query will return rows where FirstName is precisely ‘John’. The = operator matches only the exact name.

Performance Tuning Considerations

Assuming there’s an index on the FirstName column, the performance of these queries can be influenced by the way SQL Server uses this index.

Query 1: LIKE Operator

When the LIKE operator is used with a leading string (i.e., ‘John%’), SQL Server can efficiently use the index on FirstName to seek to the right position and then scan until it finds names that don’t match the condition. This efficient use of an index seek operation, followed by an index scan, can make this query fast, even on large tables.

Query 2: = Operator

For the second query, SQL Server can use an index seek operation to quickly find rows with FirstName equal to ‘John’. This operation is very efficient on large tables and is likely to be faster than the first query if only a small fraction of rows have FirstName equal to ‘John’.

Bottom Line – Intricacies

While these queries may appear similar at first glance, their string comparison methods make a significant difference in the results. Furthermore, understanding the impact of your string comparison method on query performance—especially in relation to indexing—can be crucial when dealing with large amounts of data.

Always remember to test your queries in a safe environment before running them on a production database. Understanding the precise behavior of SQL operators and functions, as well as how they interact with indexes, can help you write more accurate and efficient queries.

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