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