Interview Question of the Week #017 – Performance Comparison of Union vs Union All

Here is what I just learned from email. One of the very prestigious organization asks one of their candidate following questions –

Question: If you have options to use Union or Union All – which one of the option will use keeping performance as a top most requirement for the query.

Answer: Though many of you may be surprised to know that this kind of questions can exist, I am personally not surprised to see this in an interview. Here is my answer – UNION and UNION ALL can’t be compared as they are absolutely different things – they are like apples and oranges. Just like Apples and Oranges are fruits Union and Union All our operators, but they both are there for very different purposes.

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

You can’t compare their performance as they do an absolutely different task.

Here are articles you can read for further understanding this issue.

Performance comparison: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

Different between Union and Union All: SQL SERVER – UNION ALL and UNION are Different Operation

Other relevant articles:

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

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:

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

With UNION:

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

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.

unionerror1 SQL SERVER   UNION and UNION ALL with TEXT DataType   Observation

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

unionerror2 SQL SERVER   UNION and UNION ALL with TEXT DataType   Observation

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.

puzzunion5 1 SQL SERVER   Simple Puzzle with UNION   Part 5

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.

puzzunion5 2 SQL SERVER   Simple Puzzle with UNION   Part 5

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.

puzzunion4 1 SQL SERVER   Simple Puzzle with UNION   Part 4

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.

puzzunion4 2 SQL SERVER   Simple Puzzle with UNION   Part 4

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.

puzzunion4 3 SQL SERVER   Simple Puzzle with UNION   Part 4

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?

puzzunion 33 SQL SERVER   Simple Puzzle with UNION   Part 3

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

puzzunion 1 SQL SERVER   Simple Puzzle with UNION   Part 2

The query above will return following execution plan

puzzunion 2 SQL SERVER   Simple Puzzle with UNION   Part 2

Query 2

SELECT 1
UNION ALL
SELECT 2
ORDER BY 1

The query above will return following result

puzzunion 1 SQL SERVER   Simple Puzzle with UNION   Part 2

The query above will return following execution plan

puzzunion 3 SQL SERVER   Simple Puzzle with UNION   Part 2

Query 3

SELECT DISTINCT 1
UNION ALL
SELECT DISTINCT 2
ORDER BY 1

The query above will return following result

puzzunion 1 SQL SERVER   Simple Puzzle with UNION   Part 2

The query above will return following execution plan

puzzunion 4 SQL SERVER   Simple Puzzle with UNION   Part 2

Now let us look at all the execution plans together.

puzzunion 5 SQL SERVER   Simple Puzzle with UNION   Part 2

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:

puzzunion1 SQL SERVER   Simple Puzzle with UNION

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.

puzzunion2 SQL SERVER   Simple Puzzle with UNION

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:

puzzunion3 SQL SERVER   Simple Puzzle with UNION

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)