asked
Which is faster Distinct or Group by while we remove duplicates?
Hi, Which is faster Distinct or Group by while we remove duplicates?
My experience is that they produce the exact same Query plan. You could try it yourself - display actual execution plan and compare the plans for different queries using distinct vs Group by. I haven't found any difference at all when I compare them. Probably because the optimizer is smart enough to understand that they are semantically the same.
I agree with Magnus. Beyond just the performance, my general guidance for writing code is to use what makes sense so the next person understands your purpose. In this case, since the purpose is to remove duplicates, I suggest using DISTINCT, but that's just my preference for readability. When I see GROUP BY, I assume the purpose is to compute an aggregate.
I did a test on MSSQL 2014(SP1CU4), created a table with column ID INT and added 100.000 random numbers. It created the same execution plan for DISTINCT and GROUP BY (table scan and hash match)
19 People are following this question.
