question

ashok2012 avatar image
ashok2012 asked

Which is faster Distinct or Group by while we remove duplicates?

Hi, Which is faster Distinct or Group by while we remove duplicates?
interview-questions
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There really isn't enough information to answer that.
1 Like 1 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.