When the performance of Distinct and Group By are different?

sql-server tsql group-by distinct
The Light picture The Light · May 18, 2012 · Viewed 9.7k times · Source

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

Answer

Mikael Eriksson picture Mikael Eriksson · May 18, 2012

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.

Related questions

Count number of records returned by group by

How do I count the number of records returned by a group by query, For eg: select count(*) from temptable group by column_1, column_2, column_3, column_4 Gives me, 1 1 2 I need to count the above records to get 1+1+1 = 3.

Read More
How to use group by with union in t-sql

How can I using group by with union in t-sql? I want to group by the first column of a result of union, I wrote the following sql but it doesn't work. I just don't know how to reference the …

Read More
Does T-SQL have an aggregate function to concatenate strings?

Possible Duplicates: Implode type function in SQL Server 2000? Concatenate row values T-SQL I have a view which I'm querying that looks like this: BuildingName PollNumber ------------ ---------- Foo Centre 12 Foo Centre 13 Foo Centre 14 Bar Hall 15 Bar Hall 16 Baz School 17 I …

Read More