Monday, May 21, 2012

Performance difference between DISTINCT and GROUP BY


I was wondering if the following two queries differ in performance when used in SQL Server.
select DISTINCT persNrDV, volgnrDienstverband from medewerker
select persNrDV, volgnrDienstverband from medewerker 
GROUP BY persNrDV, volgnrDienstverband

The goal of both of the above queries is to produce a list of distinct employee numbers and employment numbers from the employee table. The first query uses SELECT DISTINCT to accomplish this task, and the second query uses GROUP BY.
I ran these queries with the Execution Plan option turned on, I found that not only the results are identical, but the Execution Plans are also identical. The SQL Server Query Optimizer has the ability to decipher each query, determining what the ultimate results are, and to produce the most efficient Execution Plan possible.
So my conclusion is that there is no performance advantage using one form of the query over the other.