SQL SERVER – UNION and UNION ALL with TEXT DataType – Observation

You can find the difference between UNION and UNION ALL in the following posts Union vs. Union All – Which is better for performance?

There is one more difference between the two when a column of TEXT datatype is involved. When you use UNION for a TEXT column you will get an error, but no error for UNION ALL

Run the following code

--Error on UNION
DECLARE @TEST1 TABLE(TEXT_COLUMN TEXT)
INSERT INTO @TEST1 (TEXT_COLUMN)
SELECT 'TEST DATA - UNION'
SELECT TEXT_COLUMN FROM @TEST1
UNION
SELECT
'TEST - UNION'

Solarwinds

The error is

Msg 5335, Level 16, State 1, Line 5
The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

SQL SERVER - UNION and UNION ALL with TEXT DataType - Observation unionerror1

Now let us see second example where there is no error because we are using UNION ALL.

--No Error on UNION ALL
DECLARE @TEST2 TABLE(TEXT_COLUMN TEXT)
INSERT INTO @TEST2 (TEXT_COLUMN)
SELECT 'TEST DATA - UNION ALL'
SELECT TEXT_COLUMN FROM @TEST2
UNION ALL
SELECT 'TEST - UNION ALL'

The result is

TEXT_COLUMN
------------------------
TEST DATA - UNION ALL
TEST - UNION ALL

SQL SERVER - UNION and UNION ALL with TEXT DataType - Observation unionerror2

I find this difference pretty interesting. I recently learned about it. Just thought of asking – did you know about this or just learned along with me?

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

Solarwinds
Previous Post
SQL SERVER – Puzzle – Why Divide by Zero Error
Next Post
SQLAuthority News – Thank You for Amazing Year! – Five Sixty Seconds Video

Related Posts

13 Comments. Leave new

  • In the UNION example @TEST1 variable id of type text and atext type stores the data in a blob while the string ‘TEST – UNION’ is of varchar type. UNION operator gives the distinct values set discarding the duplicate values but UNION ALL includes all the duplicate values from all datasets. Thus, because of metadata difference in these two datasets, UNION is giving error while comparing for distinct/duplicate values.

    Reply
    • In old versions of SQL Server, error message is different.

      The text data type cannot be selected as DISTINCT because it is not comparable.

      This also proves that UNION is performing DISTINCT.

      And text data type is not supporting DISTINCT.

      Best solution is avoid text data type, because it is deprecated data type.

      Reply
  • Thank you for the nice tip !
    It is new for me.

    Reply
  • Union removes the duplicate values. But at the time of removing duplicate values, values should be belongs to same data types. But above case, first we have inserted the values to Text datatype it is same as blob..and we are tried to compare the test datatype to varchar datatype… That’s why we are received the error in first query.

    In second script UNION All, It is just concatenating(Clubbing) the data and showing.

    Reply
  • Varija tripathi
    December 30, 2014 4:26 pm

    Hi,

    I enjoyed this article. I would love to see some more such interesting article from your side.
    Thanks for writing .
    Keep up your great work!
    Regards,
    varija tripathi

    Reply
  • >> * Selva * <<
    December 30, 2014 5:28 pm

    When we use Union, it will take larger datatype of the column. In this example it will take Text as a result column type. Also it will try to sort the result set column. Text datatype won’t compatiable with this operations. So we are getting the error message.

    Reply
  • ‘Order by’ not supporting text data type.

    When we do ‘union’ then its automatically orders records to distinguish it.

    That’s why its giving error.

    Reply
  • IN oracle we can use the MINUS to pull out all the changed records only. Is there similar functionality in SQLServer?

    Reply

Leave a Reply

Menu