November 5, 2008 at 8:39 pm
Comments posted to this topic are about the item UNION error
November 6, 2008 at 2:57 am
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?
November 6, 2008 at 5:26 am
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?
November 6, 2008 at 6:39 am
What is the definition of workarround?
The Redneck DBA
November 6, 2008 at 7:05 am
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.
November 6, 2008 at 7:43 am
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
November 6, 2008 at 7:45 am
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:
November 6, 2008 at 8:02 am
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 !
November 6, 2008 at 8:06 am
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.
November 6, 2008 at 8:11 am
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 🙂
November 6, 2008 at 8:14 am
I agree with "J"!
Although I had selected "change the data type to varchar(max)" I removed it as the scenario had stated that no duplicates could be assumed. Therefore, the only proper solution (workaround) to the problem stated was to use UNION ALL -- which, by the way should have used in the first place if the data is not duplicated between the two tables.
A data type change to a table is NOT a "workaround".
November 6, 2008 at 8:16 am
Now,
69% of reponses were right (UNION ALL).
So, peasants, let's organize. Pick up your torches and pitchforks and let's all walk up to Frankenstein's castle!
November 6, 2008 at 8:22 am
JohnG (11/6/2008)
A data type change to a table is NOT a "workaround".
While I agree that a datatype change to an existing production table may not be a work-around, I do think this is an important idea for future design - to move away from the use of text datatypes and use varchar(max) instead.
November 6, 2008 at 8:30 am
Carla Wilson (11/6/2008)
JohnG (11/6/2008)
A data type change to a table is NOT a "workaround".While I agree that a datatype change to an existing production table may not be a work-around, I do think this is an important idea for future design - to move away from the use of text datatypes and use varchar(max) instead.
Totally agree that the new "max" data types should be used vs. TEXT and NTEXT for all new development work. And, where possible, they should be changed in existing applications. However, such a change is not simple as code (T-SQL or other) may be using special functions and/or methods that are only applicable to TEXT and NTEXT.
The scenario presented would indicate that it was an existing application as a "workaround" (sic) was needed. If on the other hand, the scenario had started off with "you are designing tables for a new application..." the "change the data type" answer would apply.
November 6, 2008 at 8:35 am
It was my understanding that varchar(max) was meant to be a replacement to the text data type because of the issue that this question raises as well as other like:
Being able to use varchar(max) as a local variable (can't with text)
Being able to use most string functions with it
It can still hold 2Gb of data like text can, so what would this break?
Please correct me if I'm wrong. These discussions are great!
Greg
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply