SQL Server: is there a performance overhead between '=' and 'like' operators?

Question

Is there a difference in performance between these two queries?

--= operator
SELECT COL1, COL2 
FROM DBO.MYTABLE 
WHERE COL1 = '1'

--like operator
SELECT COL1, COL2 
FROM DBO.MYTABLE 
WHERE COL1 LIKE '1'

Basically using LIKE in this case is wrong but database engine accepts this.

Answer 1

Checkout the following post.

Quote (in case it goes off-line):

My knee-jerk response was that the = would be faster, but I thought about it and realized that the query optimizer would actually see them as the same thing. A check of the query plans against a quickly-created tbFoo confirmed it. So that's what I told him.

Except that I realized a moment later that there was a major caveat - the query optimization depends on how the statement is parameterized. If it's purely ad hoc SQL and being compiled at run-time, then the statements are equivalent, but if there's going to be any plan re-use, either by including the statement in a stored proc or preparing it and executing via sp_executesql, the LIKE will impose a significant penalty.

This is because the optimizer doesn't know at compile time whether the parameter to the LIKE operator will contain a wild card, so it can't use the more specific optimization (including index selection) that it could with an = operator. So if you mostly pass parameters without wildcards, you will be executing a suboptimal query plan. Keep this in mind when designing your query!