Which is better: Distinct or Group By

Question

Which is more efficient?

SELECT theField
FROM theTable
GROUP BY theField

or

SELECT DISTINCT theField
FROM theTable

Answer 1

You can check the Execution Plan to look for the total cost of this statements. The answer may vary in different scenarios.

Answer 2

In most cases, DISTINCT and GROUP BY generate the same plans, and their performance is usually identical

Answer 3

Doesn't matter, it results in the same execution plan. (at least for these queries). These kind of questions are easy to solve, by enabling query analyzer or SSMS to show the execution plan and perhaps the server trace statistics after running the query.

Answer 4

Hmmm...so far as I can see in the Execution Plan for running similar queries, they are identical.

Answer 5

you do have to know the difference between the two and not use them interchangeably.

Answer 6

In your example, both queries will generate the same execution plan so their performance will be the same.

However, they both have their own purpose. To make your code easier to understand, you should use distinct to eliminate duplicate rows and group by to apply aggregate operators (sum, count, max, ...).