7

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.

Improve this question
3
  • 1
    In addition to being consistent about using N for Unicode and no N for varchar, please always use the schema prefix. Also, order is a keyword, and thus a terrible choice for a table name (and many would argue that a table of orders or members should not have singular names - this makes it seem like they will only ever contain exactly one row). – Aaron Bertrand Apr 7 '15 at 13:27
  • (And FWIW I was able to remove the implicit convert simply by adding OPTION (RECOMPILE) to the statement inside the procedure. I didn't have to change the collation of the table, clear the plan cache, etc.) – Aaron Bertrand Apr 7 '15 at 13:57
  • 1
    @Stackoverflowuser you might find this answer useful in understanding how/why collation is a very important. – Kin Shah Apr 7 '15 at 14:42
6

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.

Improve this answer
0
5

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.

Improve this answer
2
  • How is this different from the highest voted answer? – Colin 't Hart Apr 7 '15 at 11:44
  • The answer was incomplete and the op stated he did not know why it happened which is outlined in my response. The issue was the difference in collation between the column and parameter. – Ted Apr 7 '15 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.