Unexpected Implicit conversion in a procedure

Question

I have a procedure like this (simplified):

CREATE PROCEDURE test @userName VARCHAR(64)
SELECT * 
FROM member M
INNER JOIN order O  
ON M.MemberId=O.MemberId
WHERE M.Username = @userName

There is a non-clustered index on Username column of the Member table.

Plan cache shows an Implicit Conversion as such:

Seek Keys[1]: Prefix: [MyDatabase].[dbo].[Member].Username = Scalar Operator(CONVERT_IMPLICIT(varchar(64),[@Username],0))

I was just wondering what might be causing this implicit conversion as both the parameter and the field data type "UserName" is varchar(64)?

SP is called from framework like this:

EXEC test @Username=N'webSite.com'

Thank you.

Answer 1

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.

Answer 2

The CONVERT_IMPLICIT is occurring because you have a collation on the column which does not match the parameter's collation. So the parameter is converted to the column's collation.

To explain further - there are collation coercion rules which triggers this conversion. So if you have an implicit collation for the column and a coercible-default for the parameter then the parameter is converted to the column's collation. If both had explicit but different collations, then a collation conflict error would result.