title: Benefits of explicit type conversion?
body: <p>I am working with some queries written by someone else who (in my mind) has gone a little overboard wrapping absolutely everything in calls to Convert().</p>
  
  <p>These queries get data from a source database, and a C# program inserts that data (using System.Data.SqlClient.SqlBulkCopy) into tables in a target database.</p>
  
  <p>So, here is an abbreviated example of the type of thing I'm seeing:</p>
  
  <pre><code>Select
    Convert(nvarchar(50), t.Address1) as Address1
  , Convert(smallint, case t.language_cd
                          when 'E' then 31
                          when 'N' then 32
                          when 'D' then 38
                                   else 39
                      end) as LanguageID
  from MyTable t
  </code></pre>
  
  <p>The data types that it's converting to match the data types on the target table (So Address1 on the target table is an nvarchar(50) and LanguageID is a smallint). The source column MyTable.Address1 is a varchar(60) and for LanguageID you can see that we're converting the literals 31, 32, 38, and 39 into smallint.</p>
  
  <p>My question is: Is there any actual reason or advantage to explicitly converting every single column to the data type that matches the target column? Sure, converting a varchar(60) to an nvarchar(50) might fail. But wrapping it in convert() doesn't stop it from failing. I guess one plus would be that if a failure does occur, it will be easy for someone to see at a glance (in the query file) exactly which conversion we were trying to do. But to me that doesn't seem to justify all this clutter in the query file, especially ugly stuff like embedding a CASE statement in a Convert() call. Plus, if we were ever to update the target database to lengthen the column to 60 characters (probably a good idea but not feasible in the short term), then we'd have a mismatch between the Convert() call and the actual definition of the target column.</p>
  
  <p>So, am I missing some reason, some benefit that I get from the explicit conversion that I'm not aware of? Or is my impulse to strip this query down and let the conversion happen implicitly a good one?</p>