Like vs. "=" for matching strings using SQL Server
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.
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.