Click here to Skip to main content
13,144,309 members (34,137 online)
Click here to Skip to main content
Add your own
alternative version


2 bookmarked
Posted 8 Dec 2013

Type Preference with UNION in SQL Server

, 10 Dec 2013
Rate this:
Please Sign up or sign in to vote.
A scenario with data type of the resultant select columns with UNION.


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.


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

, and not varchar. This is a tricky scenario which needs to be explicitly taken into account for reviews or testing.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

This member doesn't quite have enough reputation to be able to display their biography and homepage.

You may also be interested in...


Comments and Discussions

GeneralThoughts Pin
PIEBALDconsult8-Dec-13 4:22
professionalPIEBALDconsult8-Dec-13 4:22 
GeneralRe: Thoughts Pin
Amey A khedekar9-Feb-14 18:04
memberAmey A khedekar9-Feb-14 18:04 
QuestionFormatting Issues Pin
Sampath Lokuge8-Dec-13 1:08
memberSampath Lokuge8-Dec-13 1:08 
AnswerRe: Formatting Issues Pin
Amey A khedekar9-Feb-14 18:05
memberAmey A khedekar9-Feb-14 18:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170915.1 | Last Updated 10 Dec 2013
Article Copyright 2013 by Amey A khedekar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid