Don't Use Union On Tables With Text Columns

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