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


UNION error


UNION error

Author
Message
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3124 Visits: 2618
Comments posted to this topic are about the item UNION error
David Wall-401510
David Wall-401510
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 35
I don't agree that using the conversion to varchar(max) is suitable... you're going to get truncated data when the data in the text fields is too big. If the conversion to varchar(max) is never going to cause truncation - why set the table up with a text datatype?
Ric Sierra
Ric Sierra
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1481 Visits: 342
I think the answer is just the change the text domain by varchar(max) -that's was my answer-
Because: "If you assume that there is no duplicate data across both tables, then which of the following are workarounds?"

If you are assuming there is not duplicate rows is not necesary UNION ALL sentence.

In other hand I think the use of UNION ALL has a better performance insted of UNION sentence because avoid the check of duplicate rows. What you think?
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2518 Visits: 2610
What is the definition of workarround?

The Redneck DBA
David Wall-401510
David Wall-401510
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 35
I suppose something that gives you the same results but by a different method - my point was that the conversion to varchar(max) isn't guaranteed to do this.
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2518 Visits: 2610
I've always thought of it as fixing the symptom of a problem without fixing the underlying cause.

Either way, I think I agree with you.

Messing with the UNION is really the only answer that is a "workarround" per se.

The Redneck DBA
Chris Harshman
Chris Harshman
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4959 Visits: 3994
Why did they have to make the answer really be a response to 2 separate questions? UNION ALL is not a workarround for a column defined as TEXT datatype. UNION vs. UNION ALL is a separate topic for a separate purpose.
Unsure
J-440512
J-440512
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 Visits: 949
Sorry - you were wrong

No. YOU ARE wrong.

It does not matter if there are duplicates or not, because the question specifically mentioned that there were no duplicates.

So "UNION ALL" is entirely satisfactory.

And modifying the structure of tables is hardly a "workaround". There are consequences to doing this.

Now, gimme my points! And double them for amends! Or else !
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1923 Visits: 1950
Chris Harshman (11/6/2008)
Why did they have to make the answer really be a response to 2 separate questions? UNION ALL is not a workarround for a column defined as TEXT datatype. UNION vs. UNION ALL is a separate topic for a separate purpose.
Unsure


I think the point is that the text datatype is not comparable, and UNION has an implicit DISTINCT. So you can use UNION ALL to avoid the DISTINCT.

Conversely, if you want to have the column be comparable, then you should use varchar(max), and use UNION to eliminate duplicates. There was also the comment that going forward, best practice will be to use varchar(max) instead of text datatype.
David Wall-401510
David Wall-401510
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 35
I wonder if the person who set the question could explain his/her thoughts behind the "answer". At least it's got us all thinking about the consequences of both data type conversion and UNION vs UNION ALL.

Not that it helps our points total at all Smile
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