SQL SERVER – UNION With TEXT DataType Returns Error but and UNION ALL Works – Reasons

Earlier I demonstrated a very interesting behavior in my blog post – UNION and UNION ALL with TEXT DataType – Observation, where we see that UNION clause with TEXT datatype returns Error but with UNION ALL it does not return an error. I did not know that earlier before I posted that blog post. When I had executed the queries I immediately learned about the reasons. While I posted the blog post I did not post the reason for this particular behavior because I just did not feel it was relevant. However, after I posted that blog post there were many different emails and comments asking for the reasons.

Before we continue this blog post, let us see the results with UNION ALL and UNION with TEXT datatypes.

With UNION ALL:

SQL SERVER - UNION With TEXT DataType Returns Error but and UNION ALL Works - Reasons unionerror2

With UNION:

SQL SERVER - UNION With TEXT DataType Returns Error but and UNION ALL Works - Reasons unionerror1

Now here is the reason:

When we use UNION ALL between SELECT statements at that time it returns results as it is but when we use UNION at that time it automatically sorts data internally, while removing duplicate data. Sorting TEXT data is not possible, hence it returns the error which we see on the screen. In another word we can say that UNION clause does ORDER BY operation on the resultset which is not supported by the TEXT datatype hence we see the error.

There are some execellent comments in the blog post, I encourage everyone to read it.

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

SQL Union clause
Previous Post
SQL SERVER – Fix – Missing “Mirroring” and “Transaction Log Shipping” option in the Database Properties
Next Post
Interview Question of the Week #001 – Script to List Foreign Key Relationships and Constraint Name

Related Posts

Leave a Reply