You are right. The datatype is determined by the order of precedence as explained in http://msdn.microsoft.com/en-us/library/ms190309.aspx . Thanks for pointing out this and i apologize everyone for making this mistake.
We can verify this by small example.
For integer datatype the order of precedence level is
1: bigint (highest precedence)
4: tinyint (lowest precedence) .
Note: For demo i have choosen only the above 4 datatype.
After running the below code Go to
DataBases -> SystemDataBases -> tempdb -> TestTable -> Columns . Its clear that column C1 is created with type bigint and C2 is created with type int based on the highest precedence in the select list.
-- Sample code
select * into TestTable from
select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2
select CAST(2 as tinyint),CAST(6 as int)
select CAST(3 as bigint),CAST(6 as smallint)
select CAST(4 as int),CAST(6 as tinyint)