Why do I get CONVERT_IMPLICIT for same data type?

Question

Consider this snippet:

CREATE TABLE #Temp ( Name varchar(100) )
GO

DECLARE @Name varchar(100)
SELECT  @Name = '123'

SELECT * FROM #Temp WHERE Name = @Name

When inspecting it's execution plan, I got a CONVERT_IMPLICIT call on @Name variable:

[tempdb].[dbo].[#Temp].[Name]=CONVERT_IMPLICIT(varchar(100),[@Name],0)

Why this happens, as I have same data types?

Answer 1

I see that CONVERT_IMPLICIT when running your script in the context of a database with a different collation than tempdb.

When running from a DB with the same collation it does not appear in the plan.

In some circumstances this can fail with the error

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

but I'm not sure of the circumstances when this implicit conversion can't be done.