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 123»»»

UNION error Expand / Collapse
Author
Message
Posted Wednesday, November 5, 2008 8:39 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 AM
Points: 2,716, Visits: 2,471
Comments posted to this topic are about the item UNION error
Post #597840
Posted Thursday, November 6, 2008 2:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Thursday, November 6, 2008 5:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 31, 2014 6:28 AM
Points: 1,426, 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?
Post #598025
Posted Thursday, November 6, 2008 6:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 1,531, Visits: 2,248
What is the definition of workarround?

The Redneck DBA
Post #598071
Posted Thursday, November 6, 2008 7:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Thursday, November 6, 2008 7:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 1,531, Visits: 2,248
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
Post #598138
Posted Thursday, November 6, 2008 7:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 1,907, Visits: 2,056
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.
Post #598143
Posted Thursday, November 6, 2008 8:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:05 PM
Points: 441, Visits: 933
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
Posted Thursday, November 6, 2008 8:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 1,577, Visits: 1,857
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.


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
Posted Thursday, November 6, 2008 8:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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 »

Add to briefcase 123»»»

Permissions Expand / Collapse