In SQL Server 2008 the INSERT statement has been enhanced which enables to insert multiple records using the VALUE clause in a single INSERT statement.

insert into #test values

(2,'D'),

('','E')

Above INSERT statement will insert two records.

In first set of values, 2 is not embedded in single quotes and that is why the data type is considered as "int". SQL Server considers that data type for the first value of all records being inserted in that INSERT statement.

First set of values inserts a record as 2, D

While inserting second record, the first value '' is implicitly converted as 0 so second set of values inserts a record as 0, E.

This question gave us a learning that we have to be careful about implicit conversion that SQL Server does.