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 Thursday, November 6, 2008 8:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:25 PM
Points: 1,187, Visits: 1,987
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".



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #598189
Posted Thursday, November 6, 2008 8:16 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 442, Visits: 940
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!
Post #598191
Posted Thursday, November 6, 2008 8:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 5, 2014 2:22 PM
Points: 1,586, Visits: 1,860
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.
Post #598200
Posted Thursday, November 6, 2008 8:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:25 PM
Points: 1,187, Visits: 1,987
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.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #598212
Posted Thursday, November 6, 2008 8:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581
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




Post #598217
Posted Thursday, November 6, 2008 8:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:25 PM
Points: 1,187, Visits: 1,987
G² (11/6/2008)
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


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.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #598223
Posted Thursday, November 6, 2008 8:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581
JohnG (11/6/2008)
G² (11/6/2008)
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


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.



That's a good point. I hadn't thought of the text specific functions.

Thanks,

Greg




Post #598236
Posted Thursday, November 6, 2008 8:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:25 PM
Points: 1,187, Visits: 1,987
G² (11/6/2008)
[quote]
That's a good point. I hadn't thought of the text specific functions.

Thanks,

Greg


Now you know why just performing an "ALTER TABLE" to change the data type could not be considered a "workaround" to the specific query problem. The entire application could fail.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #598243
Posted Thursday, November 6, 2008 8:58 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 442, Visits: 940
DOCTOR FRANKENSTEIN

OPEN UP !
Post #598244
Posted Thursday, November 6, 2008 9:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:08 PM
Points: 20,905, Visits: 32,960
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #598257
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse