SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kev Riley / UNION error / Latest Posts

RE: UNION error

L' Eomot Inversé — Fri, 08 Jan 2010 19:42:09 GMT

[quote][b]Lynn Pettis (1/26/2009)[/b][hr][quote][b]Tom.Thomson (1/26/2009)[/b][hr][quote][b]J (11/6/2008)[/b][hr][i][size="3"][b]Sorry - you were wrong[/b][/size][/i]No. [b]YOU[/b] [b]ARE[/b] 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 ![/quote]I'm a hundred percent in agreement with this reply. Plus the varchar(max) option doesn't exists in SQL 2000, which is where most SQL Server databases still are.The answer is wrong, completely wrong!Tom[/quote]Unfortunately, the question specifically stated SQL Server 2005, as shown in the following quote from the question:[quote]You have a default SQL2005 Standard.installation with 2 tables:[/quote]So this comes down to reading the WHOLE question before answering.[/quote]Mea maxima culpa! I should read more carefully, my addition about sql 2000 was totally irrelevant. BUT: union all is still entirely satisfactory, since there are known to be no duplicates (part of the probl;em, statement) - and union instead of union all will perform less well (since it has to do the unneeded check for duplicates). AND making modifications to table structure is NOT a good workaround, there are potentially many side effects. I'm not sure if it was you who originally made those comments or someone else, but that was what I was agreeing with.Tom

RE: UNION error

BudaCli — Wed, 06 Jan 2010 06:26:08 GMT

Happy wif the question and the answer...

RE: UNION error

Deepak Sharma-2311 — Tue, 09 Jun 2009 04:18:42 GMT

I am not getting the second option to change UNION to UNION all

RE: UNION error

Lynn Pettis — Mon, 26 Jan 2009 15:10:23 GMT

[quote][b]Joe Burdette (11/6/2008)[/b][hr]In MHO, making a change to the underlying table structure is NOT a workaround. (period!) Therefore, the only "workaround" solution is to change the select statement. ;)[/quote]No need to change the structure of the tables. I posted a workaround that would use a view over the tables. Derek Dongray also posted a solution whar you cast the text columns as varchar(max) in the select statements directly.The best solution, however, would have been to build the tables in SQL Server 2005 using the varchar(max) to begin with.

RE: UNION error

Lynn Pettis — Mon, 26 Jan 2009 15:06:28 GMT

[quote][b]Tom.Thomson (1/26/2009)[/b][hr][quote][b]J (11/6/2008)[/b][hr][i][size="3"][b]Sorry - you were wrong[/b][/size][/i]No. [b]YOU[/b] [b]ARE[/b] 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 ![/quote]I'm a hundred percent in agreement with this reply. Plus the varchar(max) option doesn't exists in SQL 2000, which is where most SQL Server databases still are.The answer is wrong, completely wrong!Tom[/quote]Unfortunately, the question specifically stated SQL Server 2005, as shown in the following quote from the question:[quote]You have a default SQL2005 Standard.installation with 2 tables:[/quote]So this comes down to reading the WHOLE question before answering.

RE: UNION error

L' Eomot Inversé — Mon, 26 Jan 2009 14:49:48 GMT

[quote][b]J (11/6/2008)[/b][hr][i][size="3"][b]Sorry - you were wrong[/b][/size][/i]No. [b]YOU[/b] [b]ARE[/b] 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 ![/quote]I'm a hundred percent in agreement with this reply. Plus the varchar(max) option doesn't exists in SQL 2000, which is where most SQL Server databases still are.The answer is wrong, completely wrong!Tom

RE: UNION error

thesuraj — Wed, 19 Nov 2008 10:02:38 GMT

Even varchar() has limited size to 4000.

RE: UNION error

Joe Burdette — Thu, 06 Nov 2008 11:02:37 GMT

In MHO, making a change to the underlying table structure is NOT a workaround. (period!) Therefore, the only "workaround" solution is to change the select statement. ;)

RE: UNION error

Derek Dongray — Thu, 06 Nov 2008 09:18:04 GMT

Several people have complained that changing the text column to varchar(max) is bad because you are changing the data, which is right, so I'd just do the conversion in line..[code]select id, convert(varchar(max),somestuff) as 'somestuff' from tableAunionselect id, convert(varchar(max)somestuff) as 'somestuff' from tableB[/code]I'd be wary of changing the UNION to UNION ALL since although, at present, the assumption is that there are no duplicates, I would assume this can't be guaranteed, since otherwise the query would have specified a UNION ALL in the first place!Of couse, if this is an ad hoc query, rather than production code, than anything that works (and isn't ridiculously poor in performance) is acceptable! :)

RE: UNION error

J-440512 — Thu, 06 Nov 2008 09:10:00 GMT

Now, this is a workaround I can live with. Good idea.But I am still miffed at not having my points. (grin).

RE: UNION error

Lynn Pettis — Thu, 06 Nov 2008 09:05:34 GMT

Not mentioned as an answer, but here is a workaround using varchar(max) (or nvarchar(max)); build views over the two tables and cast the text/ntext column as a varchar(max)/nvarchar(max) datatype in the select statement.There is your workaround, and it doesn't affect the underlying table or procedures that work with the text/ntext column.

RE: UNION error

J-440512 — Thu, 06 Nov 2008 08:58:30 GMT

DOCTOR FRANKENSTEINOPEN UP !

RE: UNION error

Mauve — Thu, 06 Nov 2008 08:58:25 GMT

[quote][b]G² (11/6/2008)[/b][hr][quote]That's a good point. I hadn't thought of the text specific functions.Thanks,Greg[/quote]Now you know why just performing an "ALTER TABLE" to change the data type could not be considered a "workaround" to the specific [u]query[/u] problem. The entire application could fail.

RE: UNION error

JestersGrind — Thu, 06 Nov 2008 08:52:31 GMT

[quote][b]JohnG (11/6/2008)[/b][hr][quote][b]G² (11/6/2008)[/b][hr]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 itIt 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[/quote]Yes, all of the above is true. That is why I have changed my usage when we dropped SQL Server 2000 support. As to breakage, the application could be using READTEXT, WRITETEXT, UPDATETEXT, TEXTPTR, etc. See the BOL for details.[/quote]That's a good point. I hadn't thought of the text specific functions.Thanks,Greg

RE: UNION error

Mauve — Thu, 06 Nov 2008 08:42:17 GMT

[quote][b]G² (11/6/2008)[/b][hr]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 itIt 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[/quote]Yes, all of the above is true. That is why I have changed my usage when we dropped SQL Server 2000 support. As to breakage, the application could be using READTEXT, WRITETEXT, UPDATETEXT, TEXTPTR, etc. See the BOL for details.

RE: UNION error

JestersGrind — Thu, 06 Nov 2008 08:35:31 GMT

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 itIt 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

RE: UNION error

Mauve — Thu, 06 Nov 2008 08:30:40 GMT

[quote][b]Carla Wilson (11/6/2008)[/b][hr][quote][b]JohnG (11/6/2008)[/b][hr]A data type change to a table is [u]NOT[/u] a "workaround".[/quote]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.[/quote]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.

RE: UNION error

Carla Wilson-484785 — Thu, 06 Nov 2008 08:22:13 GMT

[quote][b]JohnG (11/6/2008)[/b][hr]A data type change to a table is [u]NOT[/u] a "workaround".[/quote]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.

RE: UNION error

J-440512 — Thu, 06 Nov 2008 08:16:03 GMT

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!

RE: UNION error

Mauve — Thu, 06 Nov 2008 08:14:46 GMT

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 [u]NOT[/u] a "workaround".

RE: UNION error

David Wall-401510 — Thu, 06 Nov 2008 08:11:31 GMT

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 :)

RE: UNION error

Carla Wilson-484785 — Thu, 06 Nov 2008 08:06:09 GMT

[quote][b]Chris Harshman (11/6/2008)[/b][hr]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:[/quote]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.

RE: UNION error

J-440512 — Thu, 06 Nov 2008 08:02:28 GMT

[i][size="3"][b]Sorry - you were wrong[/b][/size][/i]No. [b]YOU[/b] [b]ARE[/b] 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 !

RE: UNION error

Chris Harshman — Thu, 06 Nov 2008 07:45:59 GMT

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:

RE: UNION error

Jason Shadonix — Thu, 06 Nov 2008 07:43:04 GMT

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.

RE: UNION error

David Wall-401510 — Thu, 06 Nov 2008 07:05:44 GMT

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.

RE: UNION error

Jason Shadonix — Thu, 06 Nov 2008 06:39:23 GMT

What is the definition of workarround?

RE: UNION error

Ric Sierra — Thu, 06 Nov 2008 05:26:41 GMT

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?

RE: UNION error

David Wall-401510 — Thu, 06 Nov 2008 02:57:29 GMT

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?

UNION error

kevriley — Wed, 05 Nov 2008 20:39:44 GMT

Comments posted to this topic are about the item [B]UNION error[/B]