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)
25 Comments. Leave new
Union will try to result unique resut set, and for that it need to do the comparison . Comparison is not possible with text data type.
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.
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.
As text datatype is stored as blob and the lengths of the two strings are unequal.
select len(‘this is just test comment’) –25
select len(‘Other test comment’) –18
As blob storage comparison is not possible it is throwing an error as not comparable.
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.
Please correct me if am wrong
Because the Text data type cannot be sorted. In UNION is used DISTINCT Sort operation.
The text data type cannot be used for distinct,union,intersect,except because it is not comparable.
You can’t do a DISTINCT operation on a TEXT field. UNION is a “UNION ALL with DISTINCT”.
The reason UNION will not work with the fields is that they are ‘Text’ data types. The UNION keyword will sort the data to remove any duplicate data. It is not possible to sort on Text’ data type fields.
UNION ALL just combines the results. It does not do any special operation after fetching data. It has to give distinct results by comparing each and every row for UNION and in this case as error says text data can’t be compared.
I believe the text field is not comparable by design in SQL as well as being deprecated and should not be used anyways.
The text, ntext, and image data types cannot be compared or sorted.
in UNION it requires to sort data so it will not work.
Becuase text data type is not supporting DISTINCT.
The text data type can not be selected as DISTINCT because it is not comparable
UNION ALL just appends the source records into a single result set.
UNION does the same as UNION ALL but the removes duplicate records. Duplicate removal means each field in each record is compared to its counterparts on all the other records. TEXT data type does not support the comparison operators. Since comparison can not be done UNION ALL fails.
Union all is unsorted, but Union requires a sort operation to filter out duplicates. The text, ntext, and image data types cannot be compared or sorted.
Hi Pinal
Seems to me that TEXT data type is incompatible in the EQUAL TO operator.
So there is no way to calculate the proper resultset for UNION operation, where naturally EQUAL operator is used.
Union discards duplicate records. In order to do that, it does a comparison. And the data type TEXT throws an error when compared. We can not compare two values of TEXT data types.
The other contributors are right about the reason for the error. You can make the error go away like this:
SELECT CAST(comment AS varchar(max)) FROM text_union
UNION
SELECT CAST(comment AS varchar(max)) FROM text_union_all
is a reference to a “text” sql type a pointer to a seperate location, and the comparison operator is not compatible with pointers.