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.

  • 1
    What are you attempting to resolve with this question, or is this just for curiosity? As you can see from your execution plans, simple queries are, well simple. Complex queries are unlikely to be able to use either mechanism since your typical complex query would use aggregates and functions such as SUM(...) that are not supported by DISTINCT.
    – Hannah Vernon
    May 26 '14 at 18:08
  • I'm trying to find out what is the best approach for performance. Sometimes there's a need to query distinct rows in complex queries, that's why I'm trying to find out what is the best way to retrieve distinct rows. I mean when there's no need to use agregate functions. Thanks!
    – Vladimir
    May 26 '14 at 18:12

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.

  • The query I put is a basic example of this kind of query. I used it to illustrate what happens sometimes when I use GROUP BY or DISTINCT.
    – Vladimir
    May 26 '14 at 21:44
  • so... add sum() to your query and check again ;) May 27 '14 at 7:01
  • Any such pair of queries, where the two oppressions are completely equivariant, should behave the same with SQL server's query planner. Anything that would complicate it would probably make the distinct version not an option anyway, or mean you need to both group then apply distinct. I trend to be careful not to use distinct in cooked queries unless absolutely required as it can sometimes hide efficiency errors (where you have an accidental Cartesian product for example) that with the distinct still give the right results. May 28 '14 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.