Why do 59% of my Execution Plans have CONVERT_IMPLICIT warnings?
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.