SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error?

You already know what UNION and UNION ALL operators do. They combine datasets while UNION keeps the distinct data UNION ALL will retain all the data. You can read more about that over here: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. Let us see why does union all work but union produces error in this puzzle.

Today we will see a puzzle which is related to Union and Union All operator and the hint is in the solution mentioned in the link here.

Let us first create a dataset

USE TempDB
GO
-- Create the first table
CREATE TABLE text_union(comment TEXT)
INSERT INTO text_union
SELECT 'this is just test comment'
GO
-- Create the second table
CREATE TABLE text_union_all(comment TEXT)
INSERT INTO text_union_all
SELECT 'Other test comment'
GO

Next we will run two SQL Script where one works and another gives error.

Following query will just work fine.

USE tempdb;
SELECT comment FROM text_union
UNION ALL
SELECT comment FROM text_union_all

However, following query will produce error. The error clearly says Text Datatype can’t be used in the UNION.

USE tempdb;
SELECT comment FROM text_union
UNION
SELECT comment FROM text_union_all

The real question to all of you is –

Why TEXT datatype is not allowed to use in the UNION operation?

Please leave your answer in the comments section. I will publish all the answers on Monday.

Hint:

• UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
• Difference Between Union vs. Union All – Optimal Performance Comparison
• Introduction and Example of UNION and UNION ALL
• Simple Puzzle Using Union and Union All
• Simple Puzzle Using Union and Union All – Answer
• Insert Multiple Records Using One Insert Statement – Use of UNION ALL
• Union vs. Union All – Which is better for performance?
• UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL

Reference : Pinal Dave (https://blog.sqlauthority.com)