Introduction
We generally use a UNION/UNION ALL statement with all the columns in the same order
and same type. Using the same type in the column is not a rule. The
data types of the selected columns are allowed to differ, and the resultant result type is derived. Below is a scenario where this
can get a bit tricky.
Explanation
Check the below script ...
Output =>
| ecode | fname | lname |
| 1 | HRfName1 | HRlName1 |
| 2 | HRfName2 | HRlName2 |
| 101 | ACfName1 | AClName1 |
| 102 | ACfName2 | AClName2 |
In
the above script, the UNION/UNION ALL statement is used with all the columns in the
same order and the same data type.
In
case the data types of the respective select columns are different, the
type of the result column(s) is decided based on the type preference.
Check SQL ..
Output =>
| Type of ecode column in result | ecode | fname | lname |
| int | 1 | HRfName1 | HRlName1 |
| int | 2 | HRfName2 | HRlName2 |
| int | 101 | ACfName1 | AClName1 |
| int | 102 | ACfName2 | AClName2 |
| int | 1001 | ITfName1 | ITlName1 |
| int | 1002 | ITfName2 | ITlName2 |
So
as we see, the type of the resultant column is integer, which is of higher
preference than nvarchar (you can refer to the complete list of type preferences
at MSDN).
As
this conversion is strictly via data type preference, it can be tricky at
times, and the implementation may fail with some live data combination...
E.g.,
see the below SQL ..
Output =>
Conversion failed when converting the nvarchar value 'AC103' to data type int.
In
the above case, the conversion failed, as the resultant type is chosen as
int
,
and not
varchar. This is a tricky scenario which needs to be explicitly taken into account for reviews or
testing.
