Using Distinct or Not

Question

In the below SQL Statement, should I be using DISTINCT as I have a Group By in my Where Clause? Thoughts?

SELECT [OrderUser].OrderUserId, ISNULL(SUM(total.FileSize), 0), ISNULL(SUM(total.CompressedFileSize), 0)
FROM 
(
 SELECT DISTINCT ProductSize.OrderUserId, ProductSize.FileInfoId, 
 CAST(ProductSize.FileSize AS BIGINT) AS FileSize, 
 CAST(ProductSize.CompressedFileSize AS BIGINT) AS CompressedFileSize
 FROM ProductSize WITH (NOLOCK)
 INNER JOIN [Version] ON ProductSize.VersionId = [Version].VersionId
) AS total RIGHT OUTER JOIN [OrderUser] WITH (NOLOCK) ON total.OrderUserId = [OrderUser].OrderUserId
WHERE NOT ([OrderUser].isCustomer = 1 AND [OrderUser].isEndOrderUser = 0 OR [OrderUser].isLocation = 1) 
AND [OrderUser].OrderUserId = 1
GROUP BY [OrderUser].OrderUserId

Answer 1

Have you had a look at the execution plan to see if there is a difference with or without the DISTINCT? This should answer your question.

Answer 2

Since the question wasn't written as clearly as possible, I am assuming you are asking if the distinct is extraneous due to the group by that exists.

If the inner query returns multiple records due to the inner join, you will need a distinct there. Otherwise the right outer join will join on more records than you intend and things like SUM() will not return the same values.

So DISTINCT is not extraneous.

Answer 3

Since we don't know exactly what you're trying to accomplish, this may not be applicable, but...

If you're getting rows that appear redundant when using a JOIN, before you add in a DISTINCT, you should double-check your ON criteria to make sure that you've got the tables joining up right. If the test isn't specific enough, you might be allowing extra rows to match up that should not.

Answer 4

Do you get different results when you remove the DISTINCT?