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

January 8, 2018

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:

SQL SERVER - Puzzle - Solution - Why Does UNION ALL Work but UNION Produces Error? puzzleunionall-800x357

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