Interested to Buy Any Domain ? << Click Here >> for more details...
In performance wise distinct is good or group by is good?
eg:select name from emp group by name;
select distinct name from emp;
- 5 Answers
- 23602 Views
- Infosys, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / madhu
This question is asked many times to me. What is difference
between DISTINCT and GROUP BY?
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
| Is This Answer Correct ? | 14 Yes | 3 No |
Answer / ghorban adabi
I have test DISTINCT and GROUP BY query on a table in my
site db with 7800 record. the results that turned GROUP BY
was faster than DISTINCT!!!
GROUP BY result -> Query took 0.0094 sec
DISTINCT result -> Query took 0.0133 sec
| Is This Answer Correct ? | 5 Yes | 0 No |
In SQL Server 2000:
-------------------
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:
--------------------
Query:
select DISTINCT Book_Title,COUNT(*) from bookdetails
Answer:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'bookdetails.Book_Title' is invalid in the select
list because it is not contained in an aggregate function
and there is no GROUP BY clause.
Example of Group By:
--------------------
Query:
select Book_Title,COUNT(*) from bookdetails group by
Book_Title
Answer:
ASP 1
C 1
C++ 1
Oracle 1
SQL Server 1
VB.Net 3
Visual Bsic 1
In this Answer, the VB.NET is Duplicate, it having this
table in 3 times.
| Is This Answer Correct ? | 5 Yes | 3 No |
Answer / anil sharma
Group apply before where clause and distinct apply after
where clause.
So group by is good.
| Is This Answer Correct ? | 4 Yes | 10 No |
Answer / hitesh kumar vyas s
Performance wise Distinct is more effective than group by.
Since in group by it has to group and then provide the
result but this is not the case in distinct
| Is This Answer Correct ? | 3 Yes | 11 No |
Data table as parameter in sql server?
Why you need indexing? Where that is stored and what you mean by schema object? For what purpose we are using view?
What does the not null constraint do?
What is order of B+tree?
after migrating the dts packg to ssis by using migrtn wizrd in 2005. iam not able to open ssis pack and getting error. what r those errors? how to resolve?
explain query execution plan
What is the security principal at the server level that represents your session?
Define outer join in sql server joins?
What is exclusive locks?
How many ways to create table-valued functions?
How to select all columns of all rows from a table with a select statement in ms sql server?
Explain transaction server distributed transaction?
-
Oracle (3259) - SQL Server (4518)
- MS Access (429)
- MySQL (1402)
- Postgre (483)
- Sybase (267)
- DB Architecture (141)
- DB Administration (291)
- DB Development (113)
- SQL PLSQL (3330)
- MongoDB (502)
- IBM Informix (50)
- Neo4j (82)
- InfluxDB (0)
- Apache CouchDB (44)
- Firebird (5)
- Database Management (1411)
- Databases AllOther (288)
