Yesterday, we discussed a simple puzzle involving the UNION operator in SQL Server. The response was overwhelming, and many of you requested another puzzle. So, here we are with another back-to-basics question involving UNION.

Let’s execute the following three queries one by one. Make sure to enable the Execution Plan in SQL Server Management Studio (SSMS) to observe the differences.

Query 1:

SELECT 1 UNION ALL SELECT 2

The above query will return the following result:

1
2

The execution plan for Query 1 will be:

SELECT
  |--Concatenation
       |--Constant Scan
       |--Constant Scan

Query 2:

SELECT 1 UNION ALL SELECT 2 ORDER BY 1

The above query will return the following result:

1
2

The execution plan for Query 2 will be:

SELECT
  |--Sort
       |--Concatenation
            |--Constant Scan
            |--Constant Scan

Query 3:

SELECT DISTINCT 1 UNION ALL SELECT DISTINCT 2 ORDER BY 1

The above query will return the following result:

1
2

The execution plan for Query 3 will be:

SELECT
  |--Sort
       |--Hash Match (Distinct Sort)
            |--Concatenation
                 |--Constant Scan
                 |--Constant Scan

Now, let’s analyze the execution plans together. Although all three queries return the same result, their execution plans are different.

Here’s the question for you: When we add the DISTINCT keyword in Query 3, it technically requires more work for SQL Server compared to Query 2. However, the execution plan shows that Query 3 uses fewer resources than Query 2. Why is that?

Please leave your answer in the comment section below. I will publish all the valid answers in next week’s blog post, giving due credit to the contributors. Don’t forget to check out the first part of this puzzle if you haven’t already.