How MS SQL Compiler Does Internal Data Type Casting
Now let's 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 convert 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 cast 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