Why do 59% of my Execution Plans have CONVERT_IMPLICIT warnings?

Yes, you read correctly, 59%. Some of these are undoubtedly programmer error where they are comparing an INTEGER with a VARCHAR value, but of the ones I've surveyed (out of the 5139 execution plans with the problem) the only conversion has been a varchar to varchar comparison, like this ...

ScalarString="CONVERT_IMPLICIT(varchar(250),[CAMMS].[Entity].[t_PersonName].[LastName] as [pn].[LastName]+', '+[CAMMS].[Entity].[t_PersonName].[FirstName] as [pn].[FirstName]+' '+[CAMMS].[Entity].[t_PersonName].[MiddleName] as [pn].[MiddleName],0)"

This part of the stored proc is concatenating name parts. The varchar(250) reference above appears to come from the fact that the Full-Name value is being inserted into a table with a column of type varchar(250)

CREATE TABLE #TmpMatter (
    ObjectType      varchar(50),
    ObjectID        int, 
    MatterEntityID int,
    CaseNumber      varchar(100) null, 
    Title           varchar(50),
    DefendantName   varchar(250) null ...

I checked COLLATION on both my database and on TempDB ... they are the same. Checked COLLATION on all text columns and that is correct. I have 12 columns whose ANSI PADDING is OFF but these CONVERT_IMPLICIT errors are showing up only on columns with it ON, so I'm not seeing any mismatch there.

One post said they fixed this problem by addressing the ANSI PADDING issue, but that can't be it ... unless my temp tables are being created with PADDING = OFF. But TempDB (and all system dbs) have PADDING = ON.

I'm stuck on this one. Anyone have any ideas?

Not every implicit conversion is a problem that needs to be fixed. Is this contributing to any actual performance issue? How do you know this?

The reason an implicit conversion comes into play here is not because of data type or collation but rather because of precision. SQL Server will come up with a guess that this expression:

LastName + ', ' + FirstName + ' ' + MiddleInitial

Will need to be:

varchar
(
  datalength(LastName) 
  + 2 
  + datalength(FirstName) 
  + 1 
  + datalength(MiddleInitial)
)

You can prove this by selecting a row from Person.Person in AdventureWorks into a new #temp table using the same expression:

Output (note that in AdventureWorks these are nvarchar, so every character counts as two. So adding up ', ' and ' ' adds 6 characters to the string:

Your conversion is happening the other way around - your expression, which is:

varchar
(
   50
   + 2
   + 50
   + 1
   + 100
)
= 203

The implicit conversion (which is totally unnecessary and effectively does nothing) is making your expression (203 characters) conform to the column (250 characters). Again, this has nothing to do with type. Not all conversions involve varchar/nvarchar.

If you want to get rid of the warning, you can wrap the expression in an explicit convert(varchar(250), or add a computed column in the source table that does the same, but that won't really change anything about how the code works or how fast it executes.

Finally, you should probably consider using nvarchar for proper names. You may not think you need it today, but you probably will at some point. Conversion is typically more painful later.

StackExchange Database Administrators Q#208814, answer score: 3