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)

SQL SERVER – UNION ALL and UNION are Different Operation

I have previously written about the difference between UNION ALL and UNION multiple times over this blog but it seems like this question never gets old and I keep on getting the question again and again.

Recently I wrote a blog post about how to Return Specific Row to at the Bottom of the Resultset – T-SQL Script where I demonstrated how to use the CASE statement in the ORDER BY clause, lots of people asked me if we can do this using UNION ALL clause. I followed up this blog post with Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2 where I demonstrated the same script with UNION ALL.

Now after the blog post, I got so many questions that why not use UNION instead of UNION ALL. Well the answer is simple; it would not work. The matter of the fact, UNION will result will return totally different result because when UNION returns results it removes the redundant data and sorts the data.

Let us see run following two queries and observe how we are getting different result when we use UNION and UNION ALL.

-- UNION ALL
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND
ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO

-- UNION
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND
ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION
SELECT
[ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION
SELECT
[ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO

You can see the result of the UNION and UNION ALL in following result. With the use of the UNION we will not get the same result. Honestly I will use my first method where I used the CASE statement in the ORDER BY clause.

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

SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

If you want to create an Indexed View, you ought to know that UNION Operation is now allowed in Indexed View. It is quite surprising at times when the UNION operation looks very innocent and seems that it cannot be used in the View.

Before an in-depth understanding this subject, let me show you a script where UNION is now allowed in Indexed View:

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000
UNION
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID2 < 1000
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
/* Above statement will thrown an error
Msg 10116, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.SampleView' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
*/
-- Aleter View to replace COUNT with BIG_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000 OR ID2 < 1000
GO
-- Now let us create Index again - this time successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO

During the script, the following ERROR would occur if you try to create the index while UNION operation is in the view:

Msg 10116, Level 16, State 1, Line 1
Cannot create index on view ‘tempdb.dbo.SampleView’ because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.

In contrast to this converting the UNION to OR operation would give the same result, plus it would allow you to create an index on the View. Well, our example is one in which we are able to re-write the script with OR clause. However, keep in mind that there can be cases where it is not possible to re-write and you might end up not using Views with Index.

In this series, I have been writing about many limitations and their explanation. Now here are my questions for you:

  • What do you think is the reason behind these limitations?
  • Why UNION is not allowed in the View?

I will publish your answer with due credit on the blog.

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

SQL SERVER – Introduction and Example of UNION and UNION ALL

It is very much interesting when I get request from blog reader to re-write my previous articles. I have received few request to rewrite my article SQL SERVER – Union vs. Union All – Which is better for performance? wi.th examples. I request you to read my previous article first to understand what is the concept and read this article to understand the same concept with example.

xe=”color:green;”>/* Create First Table */
DECLARE @Table1 TABLE (Col INT)
INSERT INTO @Table1
SELECT 1
INSERT INTO @Table1
SELECT 2
INSERT INTO @Table1
SELECT 3
INSERT INTO @Table1
SELECT 4
INSERT INTO @Table1
SELECT 5

/* Create Second Table */
DECLARE @Table2 TABLE (Col INT)
INSERT INTO @Table2
SELECT 1
INSERT INTO @Table2
SELECT 2
INSERT INTO @Table2
SELECT 6
INSERT INTO @Table2
SELECT 7
INSERT INTO @Table2
SELECT 8

/* Result of Union operation */
SELECT Col ‘Union’
FROM @Table1
UNION
SELECT
Col
FROM @Table2

/* Result of Union All operation */
SELECT Col ‘UnionAll’
FROM @Table1
UNION ALL
SELECT Col
FROM @Table2
GO

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

If you look at the resultset it is clear that UNION ALL gives result unsorted but in UNION result are sorted. Let us see the query plan to see what really happens when this operation are done.

From the plan it is very clear that in UNION clause there is an additional operation of DISTINCT SORT takes place where as in case of UNION ALL there is no such operation but simple concatenation happens. From our understanding of UNION and UNION ALL this makes sense.

There are three rules of UNION one should remember.

UNION RULES

  • A UNION must be composed of two or more SELECT statements, each separated by the keyword UNION.
  • Each query in a UNION must contain the same columns, expressions, or aggregate functions, and they must be listed in the same order.
  • Column datatypes must be compatible: They need not be the same exact same type, but they must be of a type that SQL Server can implicitly convert.

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