When you have a SQL UNION between 2 or more tables and some of these tables have columns with a text data type use UNION ALL instead of UNION.
If you use UNION you will be given the following message
Server: Msg 8163, Level 16, State 4, Line 10
The text, ntext, or image data type cannot be selected as DISTINCT.
What happens is that UNION use distinct behind the scenes and you can not use distinct on text, ntext or image data types
Run this script to see what I mean
CREATE TABLE TestUnion1 (id INT,textCol TEXT)
CREATE TABLE TestUnion2 (id INT,textCol TEXT)
GO
INSERT TestUnion1 VALUES(1,'abc')
INSERT TestUnion2 VALUES(1,'abc')
INSERT TestUnion1 VALUES(1,'aaa')
INSERT TestUnion1 VALUES(1,'zzz')
INSERT TestUnion1 VALUES(3,'abc')
--problem
SELECT * FROM TestUnion1
UNION --ALL
SELECT * FROM TestUnion2
--no problem
SELECT * FROM TestUnion1
UNION ALL
SELECT * FROM TestUnion2
DROP TABLE TestUnion1,TestUnion2
No comments:
Post a Comment