Sql-server – ny difference on performance when choosing DISTINCT or GROUP BY to bring distinct values

execution-planperformancequery-performancesql-server-2012t-sql

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query.

I've tried comparing the execution plans, but they seem to be the same for both queries.

Or does it have to do with the complexity of the query? If so, an example would be appreciable.

Example – when I run the following queries against the database it shows exactly the same execution plan for each one:

select distinct table_name
from information_schema.columns
where table_name = 'Customer';

select table_name
from information_schema.columns
where table_name = 'Customer'
group by table_name;

Execution plan for the sample queries

PS.: These simple queries are just to illustrate the question with an actual example.

Best Answer

The two queries are functionally identical so should perform the same and as you can see from the query plans SQL server has indeed spotted that there is no difference between the two.

Of course as soon as you need to perform and aggregate operations you need to use grouping instead, otherwise you'll end up having to use the "distinct" version as a derived table and joining for the other detail which is not going to be the efficient way around.

Related Question