In SQL is there any performance between using like vs equals?

Question

I am using T-SQL (SQL Server).

This is the starting code:

DECLARE @code as char(8)
SET @code = '123'  -- 123%'
DECLARE @lsUseLikeForCodeWhereCondition char(1)
IF CHARINDEX('%', @code) = 0
    SET @lsUseLikeForCodeWhereCondition = 'N'
ELSE
    SET @lsUseLikeForCodeWhereCondition = 'Y'

Is there any performance between these two statements:

 select * from mytable where idn_orn_i LIKE 
  CASE WHEN @lsUseLikeForCodeWhereCondition = 'N' THEN 
    @code
  ELSE
    @code + '%'
  END

vs

  IF  @lsUseLikeForCodeWhereCondition = 'N'
    BEGIN
        select * from mytable where idn_orn_i = @code 
    END
  ELSE 
    BEGIN
        select * from mytable where idn_orn_i Like  @code + '%'
    END

Both appear to return the same results. But where it says mytable. It is actually a join with 10 different tables. So it is not small. Mostly I am wondering if the optimizer would recognize a like WITHOUT a percent sign in the string and do an equals.

If it matters idn_orn_i is char(8).

You can establish this by looking at the query plan produced by SQL Server - see [How do I obtain a Query Execution Plan?](https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan). — Ɖiamond ǤeezeƦ, Mar 08 '16 at 20:10

score 0 · Answer 1 · answered Mar 08 '16 at 22:07

The two versions are very different and this has little to do with any differences between like and =. In the first, the pattern is an expression-based pattern, which precludes the use of indexes for the query.

In the second, you have two queries and in each, the comparisons are using constants. So, the first version will definitely use an appropriate index. And I think the second will also take advantage of an index too (the compiler should turn the constant expression into a constant so an index can be used). Note that like requires that the pattern not start with a wildcard and probably has some conditions on the collations as well.

score -1 · Answer 2 · answered Mar 08 '16 at 20:33

Short answer, if you can use the =, use the =.

If the same number of rows is being returned, it should be the same cost. The actual execution plans should more or less be equal. That being said, if you are saying where value = 'asdf' vs value like 'asdf%' and you have have a lot more rows that will be returned with the like vs the =, then the = would be faster. It would also vary based on the number of rows that need to be scanned/looked up. Which depends on your data distribution statistics or cardinality.

You should look at the execution plans to know for sure.

score -1 · Answer 3 · answered Mar 08 '16 at 20:52

Exact matches, =, tend to perform faster because the string search can be potentially invalidated from a check with just the first and/or last characters.

A like cannot discount the match from the first character if it is non-matching, for cases when a % is placed before the pattern of interest. It would search the string sequentially, potentially to the end of the string, until the match is found or not at all. Thus, is more time consuming, but useful and necessary for certain tasks.

A like, without any wild cards, would operate in the same manner as =, but the procedure for = is intrinsically more efficient.

In SQL is there any performance between using like vs equals?

3 Answers3