Distinct and Group By clause

[kim1]

Hi,

I heard that between Distinct and Group By, the Distinct is faster, it is right? If yes, why?

Anyone has an idea?

Kim Kim

 

[tlbroadbent]

In SQL 2000, a quick test showed no significant difference in selecting 35K distinct entries from a table of 625K rows. I haven't seen any other stats. It would be interesting to see test results if anyone finds some. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kim1]

Hi,

Do you know the mecanism or algorithm that SQL Server uses to perform the Distinct and Group By.

I heard that the Distinct use the same macanism of the Group By but use less resources than Distinct.

Thank you for your answer.

Kim Kim

 

[tlbroadbent]

In SQl 7 and 2000, the query plan is exactly the same for the queries I tested. The IO statistics were identical for each query in SQL 7 and 2000. The time differences were insignificant.

In my limited testing there is no difference between Distinct and Group By in a simplae query. Do you have a source of you information? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kim1]

Yes Terry,

I heard from sql-server-performance web site.

Here is the example that I found in their site:

For example, you could write your query two different ways:

USE Northwind
SELECT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
GROUP BY OrderID
or

USE Northwind
SELECT DISTINCT OrderID
FROM [Order Details]
WHERE UnitPrice > 10

Both of the above queries produce the same results, but the second one will use less resources and perform faster.

Thanks Terry


Kim

 

[tlbroadbent]

I ran the queries you listed in SQL 7 and 2000 and see no difference. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kim1]

Hi Terry,

I really appreciate your help.

Thank you VERY MUCH. Kim