Click here to Skip to main content
Click here to Skip to main content

Type Preference with UNION in SQL Server

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

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.

License

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

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralThoughts PinprofessionalPIEBALDconsult8-Dec-13 5:22 
GeneralRe: Thoughts PinmemberAmey A khedekar9-Feb-14 19:04 
QuestionFormatting Issues PinmemberSampath Lokuge8-Dec-13 2:08 
AnswerRe: Formatting Issues PinmemberAmey A khedekar9-Feb-14 19:05 

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

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

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