When the performance of Distinct and Group By are different?

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

select Value,       getdate()from YourTablegroup by UnitIDselect 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.

sql-server tsql group-by distinct

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

Example for producing different performance

And the second example:

Example for producing different result

CodeHunter