Let’s have a look at difference between distinct and group by in SQL Server .  Distinct is used to find unique/distinct records where as a group by is used to group a selected set of rows into summary rows by one or more columns or an expression. The functional difference is thus obvious.

The group by can also be used to find distinct values as shown in below query.

1_difference between distinct and group by in sql server

The two queries return same result. The group by gives the same result as of distinct when no aggregate function is present. The SQL Server query optimizer produces the same plan for both the queries as shown below.

2_difference between distinct and group by in sql server

Thus, to conclude there is a functional difference as mentioned above even if the group by produces same result as of distinct. Group  By operator is meant for aggregating/grouping rows where as distinct is just used to get distinct values.

The most important difference in my opinion is the fact that the two operations "happen" at two very different steps in the logical order of operations that are executed in a SELECT statement.

Here are the most important operations:

  • FROM (including JOIN, APPLY, etc.)
  • WHERE
  • GROUP BY (can remove duplicates)
  • Aggregations
  • HAVING
  • Window functions
  • SELECT
  • DISTINCT (can remove duplicates)
  • UNION, INTERSECT, EXCEPT (can remove duplicates)
  • ORDER BY
  • OFFSET
  • LIMIT

As you can see, the logical order of each operation influences what can be done with it and how it influences subsequent operations. In particular, the fact that the GROUP BY operation "happens before" the SELECT operation (the projection) means that:

  1. It doesn't depend on the projection (which can be an advantage)
  2. It cannot use any values from the projection (which can be a disadvantage)

Ref:

https://sqlservergeeks.com/difference-between-distinct-and-group-by-in-sql-server/