UNION error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kev Riley » UNION error

30 posts, Page 1 of 3

1 2 3 »»»

UNION error

Rate Topic

Display Mode

Topic Options

Author

Message

kevriley

Posted Wednesday, November 05, 2008 8:39 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

Comments posted to this topic are about the item UNION error

Post #597840

David Wall-401510

Posted Thursday, November 06, 2008 2:57 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 21, 2010 9:17 AM
Points: 332, 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?

Post #597966

Ric Sierra

Posted Thursday, November 06, 2008 5:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:11 AM
Points: 1,422, Visits: 248

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?

Post #598025

Jason Shadonix

Posted Thursday, November 06, 2008 6:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:06 PM
Points: 1,409, Visits: 2,027

What is the definition of workarround?

Jason Shadonix
MCTS, SQL 2005

Post #598071

David Wall-401510

Posted Thursday, November 06, 2008 7:05 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 21, 2010 9:17 AM
Points: 332, 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.

Post #598089

Jason Shadonix

Posted Thursday, November 06, 2008 7:43 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:06 PM
Points: 1,409, Visits: 2,027

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.

Jason Shadonix
MCTS, SQL 2005

Post #598138

Chris Harshman

Posted Thursday, November 06, 2008 7:45 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 1,564, Visits: 1,719

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

Post #598143

J-440512

Posted Thursday, November 06, 2008 8:02 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, September 14, 2009 5:22 AM
Points: 438, Visits: 918

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 !

Post #598169

Carla Wilson-484785

Posted Thursday, November 06, 2008 8:06 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 1,355, Visits: 1,740

Chris Harshman (11/6/2008)

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.

Post #598177

David Wall-401510

Posted Thursday, November 06, 2008 8:11 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 21, 2010 9:17 AM
Points: 332, 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 :)

Post #598186

« Prev Topic | Next Topic »

30 posts, Page 1 of 3

1 2 3 »»»

Permissions