SQL silently converts int to varchar, but then throws an error when it comes across a varchar?

Question

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.

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)

Here's an example you can run locally.

CREATE TABLE dbo.TestTable
    (
    VarcharColumn varchar(10) NOT NULL
    )  ON [PRIMARY]

insert into TestTable(VarcharColumn)
Values (1)

When you run this, it inserts just fine, SQL must silently convert that integer value into a varchar behind the scenes.

However then if you try this:

insert into TestTable(VarcharColumn)
Values (1),(2),('Hello')

SQL will throw the following error:

Conversion failed when converting the varchar value 'Hello' to data type int.

Can anybody offer an explanation into the inner workings of SQL in this case? Specifically:

1. Why SQL allows you to insert integers into varchar columns
2. Why when SQL is silently converting these values, if it runs across an actual varchar it will try to convert it to int.

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.

Answer 1

This is what happened. When SQL Server ran, it went through the rows

 (1),(2),('Hello')

and realized that there are more than one data type there. Hence it converted it to the one with the highest precedence (int vs varchar).

That is why you got the error.

See more about Data Type Precedence here

https://msdn.microsoft.com/en-us/library/ms190309.aspx