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:

With UNION:

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 (http://blog.sqlauthority.com)

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'

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.

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

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 (http://blog.sqlauthority.com)

SQL SERVER – Simple Puzzle with UNION – Part 5

It seems that my yesterday’s Simple Puzzle with UNION intrigued many readers. Here is my fifth and final part of this Puzzles with UNION. I strongly suggest to read yesterday’s puzzle before continuing today’s puzzle as it is very much based on yesterday’s puzzle.

Well, Now if you have solved yesterday’s puzzle today’s puzzle will be a bit easy for you.

Here are two almost similar queries and both of them give us an almost identical answer. However, their execution plans are very different.

Query 1

SELECT *
FROM (
SELECT '1' AS Col
UNION ALL
SELECT '2') t
WHERE t.Col <> 1

Now let us observe the execution plan for this query.

When we pay attention to the execution plan of this query – we can notice that there is implicit conversion as well as there is a warning which is related to implicit conversion. Additionally, when you see the filter it also contains Not Equal To operator which we have used in the query as well.

Query 2

SELECT *
FROM (
SELECT 1 AS Col
UNION ALL
SELECT 2) t
WHERE t.Col <> '1'

Now when you execute both of the queries.

When we play attention to the execution plan of this query – we can notice that there is no implicit conversion even though the data type of the column and variable is mismatched to each other. Additionally, there is interesting behavior of the filter operator as well. In this case, the Not Equal To operator is automatically converted to Equal To operator.

Now here is the question back to you -

Why Query 1 and Query 2 even though looks identically and in theory has almost same logic their behavior in terms with execution plan is so different?

Please leave your answer in the comment section. Next week, I will compile all the answers and will post the valid answer with due credit.

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

SQL SERVER – Simple Puzzle with UNION – Part 4

When I started to write Simple Puzzle with UNION last week, I had no clue this will be extremely popular blog post and will turn into a mini-series. Earlier I wrote three different blog posts on this subject and they are very well received.

All the three blog posts which I wrote earlier are kind of back to basics. There are few readers who have requested to post something which make them think. Well for those who requested here is another fourth puzzle in the same series.

Here are three different script which are using UNION, Subquery and NOT EQUAL TO operator.  Execute all the three queries together. All the three queries will give the same result.

Query 1

SELECT *
FROM (
SELECT 1 AS Col
UNION ALL
SELECT 2) t
WHERE t.Col <> 1

Now let us check the execution plan. You will notice that execution plan converts Not Equal To operate to Equal To Operator and will compare the integer 2.

Query 2

SELECT *
FROM (
SELECT ‘1’ AS Col
UNION ALL
SELECT ‘2’) t
WHERE t.Col <> ‘1’

Now let us check the execution plan. You will notice that execution plan converts Not Equal To operate to Equal To Operator and will compare the integer 2.

Query 3

SELECT *
FROM (
SELECT ‘1’ AS Col
UNION ALL
SELECT ‘2’) t
WHERE t.Col <> 1

Now let us check the execution plan. You will notice that execution plan keeps Not Equal To operate as it is and compare it with 1 only.

Here is the question for you -

Why does the Query 3 does not convert Not Equal to Operator unlike Query 1 and Query 2?

If you know the valid answer please leave that in the comment section and I will publish it with due credit next week.

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

SQL SERVER – Simple Puzzle with UNION – Part 3

Earlier last week I had two simple puzzles related to UNION clause and the response to those puzzles have been amazing. Lots of email I have received that people wants me to post such basics puzzle again. Well here is one more puzzle which uses UNION and tests your basic knowledge. However, before you continue for today’s puzzle, I suggest you to read earlier two puzzles. If you have not participated in this earlier two puzzle. Please go ahead, there is no prize for winning besides satisfaction you can get when you get the basics correct.

Now let us see today’s puzzle. There are three different script in this puzzle. Out of following three T-SQL script only one of the script is correct and other two are incorrect. Try to answer today’s puzzle without executing the queries in the SQL Server Management studio. Here is the question for you?

Q) Which of the following query is valid query and WHY?

Query 1

SELECT *
FROM (
SELECT 1
UNION ALL
SELECT 2) t

Query 2

SELECT *
FROM (
SELECT 1 AS Col
UNION ALL
SELECT 2) t

Query 3

SELECT *
FROM (
SELECT 1
UNION ALL
SELECT 2 AS Col) t

Only one of the above query will return following result – other queries will throw an error. Why?

Please leave your answer as a comment. Next week, I will post answer to all the puzzles and will publish all the valid answers with due credit.

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

SQL SERVER – Simple Puzzle with UNION – Part 2

Yesterday we had very easy kind of Back to Basics Puzzle with UNION and I have received tremendous response to the simple puzzle. Even though there is no giveaway due to sheer interest in the subject, I have received many replies. Due to all the request, here is another back to the basic question with UNION again.

Let us execute following three query one by one. Please make sure to enable Execution Plan in SQL Server Management Studio (SSMS).

Query 1

SELECT 1
UNION ALL
SELECT 2

The query above will return following result

The query above will return following execution plan

Query 2

SELECT 1
UNION ALL
SELECT 2
ORDER BY 1

The query above will return following result

The query above will return following execution plan

Query 3

SELECT DISTINCT 1
UNION ALL
SELECT DISTINCT 2
ORDER BY 1

The query above will return following result

The query above will return following execution plan

Now let us look at all the execution plans together.

When you look at closely at all the resultset – they all returns the same result. When we see their execution plan they are very different from each other. Now here is the question back to you.

Question: When we add DISTINCT in Query 3 it is technically more work for SQL Server to do than Query 2. However, the execution plan demonstrates that Query 3 is using much lesser resources than Query 2. WHY?

Please leave your answer in the comment section. I will publish all the valid answer in the blog next week with due credit.

Do not miss to checkout the part 1 of this puzzle.

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

SQL SERVER – Simple Puzzle with UNION

It has been a long time since played a simple game on SQLAuthority.com. Let us play a simple game today. It is very simple puzzle but indeed a fun one.

First let us execute following SQL.

Query 1:

SELECT 1
UNION ALL
SELECT 1
ORDER BY 1

It will return following result:

Now try to execute the following query and guess the result:

Query 2:

SELECT 2
UNION ALL
SELECT 2
ORDER BY 2

When you execute the same it gives error that:

Msg 108, Level 16, State 1, Line 4
The ORDER BY position number 2 is out of range of the number of items in the select list.
Msg 104, Level 16, State 1, Line 4
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Now let us execute following query and guess the result:

Query 3:

SELECT 2 AS '2'
UNION ALL
SELECT 2 AS '2'
ORDER BY '2'

Above query will return following result:

Here is the question back to you – Why does a Query 2 returns error but Query 3 returns result successfully?

Just leave a comment with the answer – I will post the answer with due credit in future blog posts.

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