When the performance of Distinct and Group By are

问题:

I know in simple queries the performance and execution plans of the Distinct and Group By are almost the same.

e.g.

SELECT Name FROM NamesTable GROUP BY Name
SELECT DISTINCT Name FROM NamesTable

But I've read in some scenarios their performance would be different e.g. in subqueries, etc?

So, could you make some examples or explain some scenarios where their performance are different?

Many thanks

回答1:

If you include a calculated value in the field list you will see a difference in the execution plan.

select Value,
       getdate()
from YourTable
group by UnitID

select distinct
       Value,
       getdate()
from YourTable

The group by query aggregates before it computes the scalar value. The distinct query computes the scalar value before the aggregate.

回答2:

Here are 2 examples, one for producing a different result and the other for a different performance:

And the second example: