Type Preference with UNION in SQL Server

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.