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

5 years ago

Last week, I asked a very simple puzzle about SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error?. The puzzle was very simple and interesting both together. I got quite a few valid answers to the puzzle so far. You may check original puzzle post to see all the valid answers. In this blog post we will quickly discuss the answer of our puzzle.

During the puzzle, I have asked a very simple question, which can be explained with the image here:

The question was – Why TEXT datatype is not allowed to use in the UNION operation?

Solution to Puzzle

Let me quote few of the valid answer from the original post –

Tim Monfries – UNION must perform a comparison operation to determine uniqueness whereas UNION ALL does not since it returns all records. Since the text datatype is not comparable, UNION’s comparison-for-uniqueness operation cannot be performed and thus the query fails.

Ruslan Aleksandrovic – UNION need to select all values from second select statement which not in first select statement and need to compare values. TEXT data type is very hard to compare. UNION ALL do not compare and just select ALL values from all statements.

Sakthi Balaji – It will throw error while using UNION operator because, the UNION operator in background uses SORT physical operator to identify the DISTINCT data between Datasets. Since the larger datatypes like TEXT, NTEXT cannot be used for SORT operations it will throw error while using UNION & ORDER BY on these datatypes. We can see the details in the Query Execution plan.

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

SQL Scripts, SQL Server, SQL Union clause

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

SQL SERVER – Puzzle – Strange Behavior With Extra Comma in Create Table Statement

1 Comment. Leave new

Parth Sheth
September 11, 2018 2:16 am
Union here will throw an error as it compares value on both the columns of different tables and tries to find Uniqueness to Sort the result. Since we have Text data type which is hard to compare and it is not really required in this scenario. Whereas Union All does not look for Distinct value and will return all the records from both the rows of different tables…
Reply