Has anybody ever heard of the LIKE operator not matching exact strings? For example I would expect "WHERE lastname LIKE 'Smith%'" to return records like "Smith" and "Smithe". But I'm running into an issue where only "Smithe" is returned, not "Smith". The lastname column is an nvarchar.
One workaround I'm using is "WHERE lastname + ' ' LIKE 'Smith%'". It works, but this is causing a huge performance hit (around 20 times slower). I've tried PATINDEX, instead of LIKE, but it's not very fast either.
I'm looking for any suggestions, articles about this problem, or any confirmation of this problem.