Like vs. "=" for matching strings using SQL Server

You are correct. There is no benefit in using LIKE unless you are doing wild card matching. In addition, using it without wildcard could lead to the use of an inefficient queryplan.

Sunny almost got it right :)

Run the following in QA in a default install of SQL2005

select * from sysobjects where name = 'sysbinobjs   '-- returns 1 rowselect * from sysobjects where name like 'sysbinobjs   '-- returns 0 rows

So, LIKE does not match on trailing spaces, on the query plan side both perform almost equally, but the '=' join performs a tiny bit better.

An additional thing you MUST keep in mind when using LIKE is to escape your string properly.

declare @s varchar(40) set @s = 'escaped[_]_%'select 1 where 'escaped[_]_%'  like @s --Return nothing = BAD set @s = '_e_s_c_a_p_e_d_[___]___%' select 1 where 'escaped[_]_%'  like @s escape '_'--Returns 1 = GOOD

In general people do not use LIKE for exact matching, because the escaping issues cause all sorts of complications and subtle bugs, people forget to escape and there is a world of pain.

But ... if you want a real exact match that is efficient, LIKE can solve the problem.

Say, you want to match username to "sam" and do not want to get "Sam" or "Sam " and unfortunately the collation of the column is case insensitive.

Something like the following (with the escaping added) is the way to go.

select * from sysobjectsWHERE name = 'sysbinobjs' and name COLLATE Latin1_General_BIN LIKE 'sysbinobjs'

The reason you do a double match is to avoid a table scan.

However ....

I think the varbinary casting trick is less prone to bugs and easier to remember.

If no wildcards are used, then the difference is, that "=" makes an exact match, but LIKE will match a string with trailing spaces (from SSBO):

When you perform string comparisons with LIKE, all characters in the pattern string are significant, including leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.