Which one is better, why ?

Posted by Devanand under Sql Server on 5/4/2010 | Views : 2502 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi dude

I have been asked one question like :

Which one is better to avoide the duplicate of data for the following scenario ?

table name : tbl_erpsale

query: 1
select distinct SaleDate from tbl_erpsale

query: 2
select SaleDate from tbl_erpsale group by SaleDate

In my above scenario, which one is best. My seniors saying like "group by" is better than the "distinct". But, they don't know the exact reason. But they using this way only

could anybody explain me the reason ? please ?

yours
Devanand

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Diya0076 on: 5/4/2010 [Member] Starter

0	To avoid duplicate data distinct is used and group by is used with aggregate functions.. just search on web the difference b\|W this both .. and also the examples to understand their meaning more perfectly Devanand, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Er.deepakdalal on: 5/4/2010 [Member] Starter

0	Hi Devanand I think in this situation distinct is faster then the Group By and the difference between them is :- A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster. Example of DISTINCT: SELECT DISTINCT Employee, Rank FROM Employees Example of GROUP BY: SELECT Employee, Rank FROM Employees GROUP BY Employee, Rank Example of GROUP BY with aggregate function: SELECT Employee, Rank, COUNT() EmployeeCount FROM Employees GROUP BY Employee, Rank Devanand, if this helps please login to Mark As Answer*. \| Alert Moderator

Hi Devanand

I think in this situation distinct is faster then the Group By and the difference between them is :-

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:
SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:
SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:
SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nishithraj on: 5/5/2010 [Member] Bronze

0	If there is only one table and one group, it's better to use distinct instead of group by. Group by is specially meant for grouping data as the name mentioned. So if there is no grouping required, distinct provides better perfomance than group by. If it's vice versa go for grouping. Mark this as answer, if it is..... With regards Nishithraj Narayanan Devanand, if this helps please login to Mark As Answer. \| Alert Moderator

If there is only one table and one group, it's better to use distinct instead of group by.

Group by is specially meant for grouping data as the name mentioned. So if there is no grouping required, distinct provides better perfomance than group by. If it's vice versa go for grouping.

Mark this as answer, if it is.....

With regards
Nishithraj Narayanan

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts