I have a query that is used to lookup data for a customer based of an email address, the column in the table is stored as a varchar(100). In the query if I put in my where clause as
WHERE CD.Email = 'Anyone@Anywhere.com'
then the query returns sub second, and the execution plan has all the stuff i'd expect,. nested loops etc.
However if I then declare a variable called @Email VARCHAR(100) and set it to equal 'Anyone@Anywhere.com'
and then change my where clause to be
WHERE CD.Email = @Email
then my query takes 1 minute 45 seconds +, and the execution plan is filled with hatch matches, obviously these hash matches slow the query down, but why would the execution plan be different between a string and a parameter that SQL knows is a string? Even if I build a procedure with all the sql included then the results come out the same.
Thanks in advance.