Now lets take few test to get to the conclusion;

See how the MS SQL compiler works, MS SQL compiler is smart enough to do the conversion (implicit type casting) for columns.

For 'Test 1' MS SQL compiler converts all values of 'ID' column to 'INT', how it decides to convert is simple it has 2 types of values here, 'INT' and 'VARCHAR', since 'INT' has higher precedence than 'VARCHAR' it tries to cast varchar value ('1') to 'INT' which is done successfully hence compiler executed it as successful;

--1) TEST 1:
SELECT    '1' AS ID, 'A' AS VAL, CAST('20120601' AS DATE) DATE1    UNION ALL
SELECT    2, 'B', CAST('20120602' AS DATE) DATE1
--Run Successfully

For 'Test 2', 'TEST 3', 'TEST 4' MS SQL compiler converts all values of 'ID' column to 'INT', how it decides to convert, it has 2 types of values here, 'INT' and 'VARCHAR' and a 'NULL' value, since 'INT' has higher precedence than 'VARCHAR' it tries to cast varchar value ('1') to 'INT' as described in 'TEST 1', while casting data types compiler converts from lower to higher if the casting can be done otherwise it will raise error / exception.
For column 'VAL' it has type 'VARCHAR' and a 'NULL' value. It then cast 'NULL' value to 'VARCHAR' which is done successfully. For column 'DATE1' it has data of type 'DATE' and a 'NULL' value which is then converted to 'DATE' data type successfully.

--2) TEST 2:
SELECT    '1' AS ID, 'A' AS VAL, CAST('20120601' AS DATE) DATE1    UNION ALL
SELECT    2, 'B', CAST('20120602' AS DATE)    UNION ALL
SELECT    NULL, NULL, NULL
ORDER BY    ID
--Run Successfully

--3) TEST 3:
SELECT    '1' AS ID, 'A' AS VAL, CAST('20120601' AS DATE) DATE1    UNION ALL
SELECT    2, 'B', CAST('20120602' AS DATE)    UNION ALL
SELECT    NULL, NULL, NULL
ORDER BY    VAL
--Run Successfully

--4) TEST 4:
SELECT    '1' AS ID, 'A' AS VAL,CAST('20120601' AS DATE) DATE1    UNION ALL
SELECT    2, 'B', CAST('20120602' AS DATE)    UNION ALL
SELECT    NULL, NULL, NULL
ORDER BY    DATE1
--Run Successfully

For 'Test 5' MS SQL compiler converts all values of 'ID' column to 'INT', reason is similar to 'TEST 1'

--5) TEST 5:
SELECT    '1' AS ID    UNION ALL
SELECT    2    UNION ALL
SELECT    NULL
ORDER BY    ID
--Run Successfully

For 'Test 6' MS SQL compiler tries to converts all values of 'ID' column to 'INT' but since it fails to cast value 'A' to 'INT' , it will raise an error / exception. Also note compiler does type casting in higher data type not from higher precedence data type to lower.

--6) TEST 6:
SELECT    '1' AS ID    UNION ALL
SELECT    2    UNION ALL
SELECT    NULL    UNION ALL
SELECT    'A'
ORDER BY    ID
--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'A' to data type int.

For 'Test 7' MS SQL compiler converts all values of 'VAL' column to 'VARCHAR', reason is similar to 'TEST 2'

--7) TEST 7:
SELECT    '1' AS VAL    UNION ALL
SELECT    '2'    UNION ALL
SELECT    NULL
ORDER BY    VAL
--Run Successfully

For 'Test 7' MS SQL compiler converts all values of 'VAL' column to 'VARCHAR', reason is similar to 'TEST 2', Here is cast varchar values ('1','2') to higher precedence data type i.e; 'INT'. Then 'NULL' value will be casted to 'INT' successfully.

--8) TEST 8:
SELECT    '1' AS VAL    UNION ALL
SELECT    '2'    UNION ALL
SELECT    NULL    UNION ALL
SELECT    3
ORDER BY    VAL
--Run Successfully

For 'Test 9' MS SQL compiler converts all values of 'DATE1' column to 'DATE', reason is similar to 'TEST 2'

--9) TEST 9:
SELECT    CAST('20120601' AS DATE) AS DATE1    UNION ALL
SELECT    CAST('20120602' AS DATE)    UNION ALL
SELECT    NULL
ORDER BY    DATE1
--Run Successfully

For 'Test 10' MS SQL compiler tries to converts all values of 'DATE1' column to 'DATE', but since it fails to cast value (3) to 'DATE' , it will raise an error / exception. Also note compiler does type casting in higher data type not from higher precedence data type to lower.

--10) TEST 10:
SELECT    CAST('20120601' AS DATE) AS DATE1    UNION ALL
SELECT    CAST('20120602' AS DATE)    UNION ALL
SELECT    NULL    UNION ALL
SELECT    3
ORDER BY    DATE1
--Msg 206, Level 16, State 2, Line 1
--Operand type clash: int is incompatible with date

For 'Test 11' MS SQL compiler tries to converts all values of 'DATE1' column to 'DATE', but since it fails to cast varchar value ('A') to 'DATE' , it will raise an error / exception. Also note compiler does type casting in higher data type not from higher precedence data type to lower.

--11) TEST 11:
SELECT    CAST('20120601' AS DATE) AS DATE1    UNION ALL
SELECT    CAST('20120602' AS DATE)    UNION ALL
SELECT    NULL    UNION ALL
SELECT    'A'
ORDER BY    DATE1
--Msg 241, Level 16, State 1, Line 1
--Conversion failed when converting date and/or time from character string.


Conclusion    : Now getting conclusions from above tests, we can say when there is data of multiple data types compiler took the higher precedence data type from that specific set and tries to cast all other values to chosen data type (i.e; higher precedence) from that set. If it fails to convert any of the value it will raise and error / exception. If it cast all of them successfully then, in the end if data contains any 'NULL' values it casts those to that chosen data type as well.

Hope this helps you given a better understanding of how MS SQL compiler does type casting internally.

For Data Type precedence please refer below link;

http://msdn.microsoft.com/en-us/library/ms190309%28v=sql.105%29.aspx

Open to hear from you about this article.

Thanks, Hasham Niaz