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

SQL SERVER - Simple Puzzle with UNION - Part 2 puzzunion-1

The query above will return following execution plan

SQL SERVER - Simple Puzzle with UNION - Part 2 puzzunion-2

Query 2

SELECT 1
UNION ALL
SELECT 2
ORDER BY 1

The query above will return following result

SQL SERVER - Simple Puzzle with UNION - Part 2 puzzunion-1

The query above will return following execution plan

SQL SERVER - Simple Puzzle with UNION - Part 2 puzzunion-3

Query 3

SELECT DISTINCT 1
UNION ALL
SELECT DISTINCT 2
ORDER BY 1

The query above will return following result

SQL SERVER - Simple Puzzle with UNION - Part 2 puzzunion-1

The query above will return following execution plan

SQL SERVER - Simple Puzzle with UNION - Part 2 puzzunion-4

Now let us look at all the execution plans together.

SQL SERVER - Simple Puzzle with UNION - Part 2 puzzunion-5

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

,
Previous Post
SQL SERVER – Simple Puzzle with UNION
Next Post
SQL SERVER – Weekly Series – Memory Lane – #045

Related Posts

28 Comments. Leave new

  • MERGE JOIN internally use SORTING technique while merging the input data. Also DISTINCT itself sorts the data internally, so SORT operation time is reduced here.

    Reply
  • HI PINAL,
    the SECOND query,”SORT” operation takes place,
    hence the sorting took place in tempdb and we got the
    estimated I/O cost which is greater than 0 this make
    the query use little more resource than other query

    In the THIRD query the select statement is already sorted with distinct operator
    and then the sorted result set is merged using merge JOIN concatenation,
    since there is no any writes or reads required here so this query take little
    amount of resource

    Reply

Leave a Reply

Menu