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
- 21642 Views
- Infosys, I also Faced
- E-Mail Answers
Answer Posted / selvaraj.v
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 |
Post New Answer View All Answers
Can foreign key be duplicate?
How to insert and update data into a table with "insert" and "update" statements?
What are the advantages of mirroring?
Explain Geography datatype in SQL Server
Can you explain what is indexed view? How to create it?
What is scalar user-defined function?
What is the process of normalising?
What are the basic functions for master, msdb, model, tempdb databases?
What is sql server query analyzer?
What happens if null values are involved in arithmetic operations?
Explain atomicity?
What are synonyms?
What are the elements of dbms?
What are pessimistic lock and optimistic lock?
Do you know what are acid properties?
