which is fast distinct or group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

which is fast distinct or group by

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-25 : 07:41:20

Hi,

I want two ask that which is best method to return unique records

distinct or group by

For example below queries

select distinct [order_c] from Table_8

Or

select [order_c] from Table_8

group by [order_c]

pls exaplain me the reason

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-25 : 08:16:32

They both generate the exact same execution plan and they execute the same.
Easy to check. Run them both in management studio with the execution plan enabled and have a look at the results.

--
Gail Shaw
SQL Server MVP

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-25 : 10:45:49

That is not to say that DISTINCT and GROUP BY will always be interchangeable and result in the same plan. One situation where results are bound to be significantly different is when there is a UDF in the SELECT clause and the argument of the UDF is a column you are grouping by.

EDIT:
you can't go wrong following Gail's advice and check/test both scenarios.

Be One with the Optimizer
TG

ForkandBeard
Starting Member

10 Posts

Posted - 2008-10-25 : 11:03:22

Also worth noting: distinct is applied to the entire record and not just a single column. In your example this is not apparent as you are only selecting a single column. (You do say you're after a unique record, so I guess you're aware of this, but I thought I'd say so, just in case you weren't)

Many Thanks
Mitchell
www.forkandbeard.co.uk

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-25 : 15:22:02

quote:
Originally posted by TG

That is not to say that DISTINCT and GROUP BY will always be interchangeable and result in the same plan.

Indeed.

In this case, where the group by is on all columns that are in the select and there are no aggregations, then the plans should be identical.

My personal preference in this kind of situation is to use distinct. Not because it's faster, but because it makes it very clear to anyone reading the code what is been done. I prefer to keep group by for when there are aggregations been done..

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources