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)

About these ads

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 – Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2

“How do I return a  few of my resultset rows at the bottom of the entire resultset?”

I was previously asked this question and my response was that we can do this by using the CASE statement in the ORDER BY clause and I wrote a blog post describing the same over here SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script. In the blog post I had mentioned that there is an alternative method of UNION ALL. There have been few emails and comments regarding how to use UNION ALL in this situation hence I decided to write this blog post demonstrating the same.

Let us see the scenario one more time. In this following diagram you can notice that there are two rows which are with ProductID 712 and 715. The entire resultset is ordered by column ProductID. Now our final requirement is that we want row 715 to be the second last row in the resultset and 712 as a very last row in the resultset. Looking from outside the entire thing looks very simple however, in reality it is not as simple as it looks.

First look at the image below and see if you can come up with the solution to this problem.

Here is the script on AdventureWorks database which I have written generates the result as we have displayed in the image below.

USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID BETWEEN 707 AND 716
GROUP BY [ProductID]
GO

Now instead of writing CASE statement in ORDER BY clause we will now write UNION ALL clause. In this case if you see there are two different values which we want at the bottom of the resultset.

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

Here is the resultset which we expected. We have to use two different ORDER BY clause to get the desired result. In our case if we have more than 2 special cases like these we will need keep on adding more and more UINON clauses and that will make this script more confusing and not easy to read at all.

Now let us compare the performance of the two different queries one from earlier blog post and one from current blog post. Execute following two queries together and check their execution plan. In the execution plan can be enabled by using CTRL + M keyword.

-- Method 1 - CASE and ORDER BY
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID BETWEEN 707 AND 716
GROUP BY [ProductID]
ORDER BY CASE WHEN [ProductID] = 715 THEN 1
WHEN [ProductID] = 712 THEN 2 ELSE 0 END
GO
-- Method 2 - 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

You will clearly notice that the solution with CASE and ORDER BY is a much better scenario than using UNION ALL clause.

So far we have seen two examples 1) CASE and ORDER BY clause and 2) UNION ALL clause. If you know any other trick to get the similar result, please leave a comment and I will post this on my blog with due credit.

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