Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

T-SQL Expand / Collapse
Author
Message
Posted Tuesday, March 22, 2011 9:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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
Post #1082084
Posted Tuesday, March 22, 2011 9:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 4:10 PM
Points: 554, Visits: 1,208
Great question.

UMG,

Thanks for the link, that's good to know for sure.


Post #1082087
Posted Tuesday, March 22, 2011 10:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1082098
Posted Tuesday, March 22, 2011 12:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
This question is worth more than a point

Mohammed Moinudheen
Post #1082206
Posted Wednesday, March 23, 2011 12:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 30, 2014 9:53 PM
Points: 157, Visits: 294
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
Post #1082476
Posted Wednesday, March 23, 2011 12:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:43 AM
Points: 47, Visits: 57
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.
Post #1082481
Posted Wednesday, March 23, 2011 12:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 30, 2014 9:53 PM
Points: 157, Visits: 294
I am sorry i couldn't get your question. Can you pls explain you question in detail.
Thanks
Post #1082484
Posted Wednesday, March 23, 2011 1:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:43 AM
Points: 47, Visits: 57
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..
Post #1082501
Posted Wednesday, March 23, 2011 2:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 30, 2014 9:53 PM
Points: 157, Visits: 294
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
Post #1082522
Posted Wednesday, March 23, 2011 2:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
Nice question, thanks.



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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1082530
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse