title: SQL silently converts int to varchar, but then throws an error when it comes across a varchar?
body: <p>I came across an issue in a SQL script recently, where an insert statement (that was inserting multiple rows) had a column of type varchar(10) and the rows of data it was inserting were passing in ints.</p>
  
  <p>However, when I added a new row of data to insert, and used a varchar in the varchar column, SQL tried to convert it to an int, even though the column is of type varchar(10)</p>
  
  <p>Here's an example you can run locally.</p>
  
  <pre><code>CREATE TABLE dbo.TestTable
      (
      VarcharColumn varchar(10) NOT NULL
      )  ON [PRIMARY]
  
  insert into TestTable(VarcharColumn)
  Values (1)
  </code></pre>
  
  <p>When you run this, it inserts just fine, SQL must silently convert that integer value into a varchar behind the scenes.</p>
  
  <p>However then if you try this:</p>
  
  <pre><code>insert into TestTable(VarcharColumn)
  Values (1),(2),('Hello')
  </code></pre>
  
  <p>SQL will throw the following error: </p>
  
  <blockquote>
    <p>Conversion failed when converting the varchar value 'Hello' to data
    type int.</p>
  </blockquote>
  
  <p>Can anybody offer an explanation into the inner workings of SQL in this case? Specifically:</p>
  
  <ol>
  <li>Why SQL allows you to insert integers into varchar columns</li>
  <li>Why when SQL is silently converting these values, if it runs across an actual varchar it will try to convert it to int.</li>
  </ol>
  
  <p>I understand how to fix this issue and how to avoid it in the first place, but I am looking for an explanation as to why SQL works this way.</p>