T-SQL

Question

T-SQL

Viewing 15 posts - 31 through 45 (of 48 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

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 SSCrazy Points: 2565 More actions · Answer 2

Great question.

UMG,

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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[/url]
Learn Extended Events

M&M SSC-Insane Points: 21700 More actions · Answer 4

This question is worth more than a point 😀

M&M

Gopi S Ten Centuries Points: 1091 More actions · Answer 5

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 SSC Enthusiast Points: 171 More actions · Answer 6

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 Ten Centuries Points: 1091 More actions · Answer 7

I am sorry i couldn't get your question. Can you pls explain you question in detail.

Thanks

pksutha SSC Enthusiast Points: 171 More actions · Answer 8

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 Ten Centuries Points: 1091 More actions · Answer 9

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 SSC Guru Points: 259085 More actions · Answer 10

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 11

greated question

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 12

Hi,

It's really very good question. thanks for sharing.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 13

Note:
insert into #test values (2,'D'),('','E') is equal to insert into #test select 2,'D' union select '','E'

Actually, its like:

insert into #test values (2,'D'),('','E') is equal to insert into #test select 2,'D' union all select '','E'

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

TomThomson SSC Guru Points: 104773 More actions · Answer 14

TomThomson

SSC Guru

Points: 104773

March 26, 2011 at 9:47 am

#1303953

Really good question. Thanks.

Tom

irshadmohideen SSC Enthusiast Points: 161 More actions · Answer 15

Excellent question. Good point emphasized when inserting with multiple value segments with single insert statement.