SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
UMG Developer
UMG Developer
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7204 Visits: 2204
Bradley Deem (3/22/2011)
UMG Developer (3/21/2011)
Interesting question, thanks, and a good reason you should always specify the data type for hardcoded items in the first set of values or in the first query when using UNION.


I'm not sure this is true. It looks to me Data Type Precendence determines the data type not the order in the UNION or VALUES clause. Consider the following.


Bradley,

Thanks for pointing that out, of course I should have known better. Here is a link to the Table Value Constructor in BOL that mentions the data type precedence: http://msdn.microsoft.com/en-us/library/dd776382.aspx
Bradley Deem
Bradley Deem
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1617 Visits: 1248
Great question.

UMG,

Thanks for the link, that's good to know for sure.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143473 Visits: 18649
thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

M&M
M&M
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12246 Visits: 3919
This question is worth more than a point :-D

M&M
Gopi S
Gopi S
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 325
Bradley,
You are right. The datatype is determined by the order of precedence as explained in http://msdn.microsoft.com/en-us/library/ms190309.aspx . Thanks for pointing out this and i apologize everyone for making this mistake.
We can verify this by small example.

For integer datatype the order of precedence level is
1: bigint (highest precedence)
2: int
3: smallint
4: tinyint (lowest precedence) .
Note: For demo i have choosen only the above 4 datatype.

After running the below code Go to
DataBases -> SystemDataBases -> tempdb -> TestTable -> Columns . Its clear that column C1 is created with type bigint and C2 is created with type int based on the highest precedence in the select list.

-- Sample code
use tempdb
go

select * into TestTable from
(
select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2
union
select CAST(2 as tinyint),CAST(6 as int)
union
select CAST(3 as bigint),CAST(6 as smallint)
union
select CAST(4 as int),CAST(6 as tinyint)
) T

Thanks again.

Gopi
pksutha
pksutha
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 58
Hi Gopi,

select * into TestTable from
(
select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2
union
select CAST(2 as tinyint),CAST(6 as int)
union
select CAST(3 as bigint),CAST(6 as smallint)
union
select CAST(4 as int),CAST(6 as tinyint)
) T


Note: Good only....

But u've given only these are the orderwise datatype based on size . but i need how i can come to know query wise.. plz will u give example and we must see the difference in that example visually.
Gopi S
Gopi S
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 325
I am sorry i couldn't get your question. Can you pls explain you question in detail.
Thanks
pksutha
pksutha
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 58
Hi Gopi,

select * into TestTable from
(
select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2
union
select CAST(2 as tinyint),CAST(6 as int)
union
select CAST(3 as bigint),CAST(6 as smallint)
union
select CAST(4 as int),CAST(6 as tinyint)
) T

Note: Practically i want to see the difference... will u give example for that..
Gopi S
Gopi S
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 325
As mentioned earlier, after executing the above code,
Go to
DataBases -> SystemDataBases -> tempdb -> TestTable -> Columns .

You can see that column C1 is created with type bigint and C2 is created with type int based on the highest precedence in the select list.

I think this could be the practical example you are expecting.

Thanks
Gopi
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144788 Visits: 13349
Nice question, thanks.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search