1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

In performance wise distinct is good or group by is good?
eg:select name from emp group by name;
select distinct name from emp;

Question Posted / venu gopal

Answers were Sorted based on User's Feedback



In performance wise distinct is good or group by is good? eg:select name from emp group by name; ..

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

In performance wise distinct is good or group by is good? eg:select name from emp group by name; ..

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 performance wise distinct is good or group by is good? eg:select name from emp group by name; ..

Answer / 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

In performance wise distinct is good or group by is good? eg:select name from emp group by name; ..

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

In performance wise distinct is good or group by is good? eg:select name from emp group by name; ..

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

Post New Answer

More SQL Server Interview Questions

How to select some specific rows from a table in ms sql server?

0 Answers  

What is sql server schema compare? How we can compare two database schemas?

0 Answers  

I create a separate index on each column of a table. What are the advantages and disadvantages of this approach? : Sql server database administration

0 Answers  

What is a data source file?

0 Answers  

How do you find the error, how can you know the number of rows effected by last SQL statement?

2 Answers  




What is bcp? When does it use?

0 Answers  

What are the types of backup and tell me the difference between full and differential backup?

4 Answers   CTS, TCS,

Does dbcc checkdb requires db to be in single_user mode? : sql server database administration

0 Answers  

What does the not null constraint do?

0 Answers  

What do you mean by 'normalization'?

0 Answers   Ernst Young, Thomson Reuters,

Where do you think the users names and passwords will be stored in sql server?

4 Answers   BirlaSoft,

Table - Products has number of products as below Productid ProductName 1 iPhone 2 iPad 3 BlackBerry Table - SalesPersonProduct has the below records Salespersonid productid S1 1 S1 2 S1 3 S2 1 S3 2 Write a SQL query that returns the number of sales for each product

2 Answers  

For more SQL Server Interview Questions Click Here
Categories