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.

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

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

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

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
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
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.


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

SQL Scripts, SQL Server, SQL Union clause
Previous Post
SQL SERVER – Puzzle – Brain Teaser – Changing Data Type is Changing the Default Value
Next Post
SQL SERVER – Puzzle – Solution – Why Does UNION ALL Work but UNION Produces Error?

Related Posts

25 Comments. Leave new

  • I think it relates to the fact that the UNION attempts find distinct values and can’t do those comparisons for TEXT datatypes.

  • Because UNION would try to remove duplicates and cannot compare text values. Is that correct?

  • Because we can’t compare text data types. In union all duplicate records are not getting eliminated hence no need for comparison, whereas in UNION duplicate records are eliminated in order to do so it has to compare the data and Text data type doesn’t allow comparison.

  • UNION tries to get distinct values for which it need to compare the values, but text is not allowed to compare in SQL Server due to performance issues, as text could be really huge files of texts, which is difficult to compare in real time. UNION ALL simply appends the rows to the resulting data set without any comparison of data

  • nvarchar types are supported in such union scenarios


Leave a Reply