Using SELECT vs SELECT DISTINCT Microsoft SQL Server

I was recently told that SELECT DISTINCT could actually speed up a Stored Procedure ...

then ....... out of the blue...... someone else told me that......

SELECT DISTINCT could actually slow down a Stored Procedure......

Can someone clarify which is it......?
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
Whether it speeds it up or slows it down, it depends on what you're trying to do. Do you want distinct values out? If so, then use distinct. If you don't, then don't. There are times when you can write a query using distinct or with sub queries or with grouping.
Using DISTINCT can have a real impact on query performance since the query plan can change to include a sort of the data so "duplicates" can be found.

If a query returns duplicates it is generally a symptom of a poorly written query so the query and in particular the joins should be reviewed to ensure they are correct and complete.  Perhaps there is an additional column that is needed in the join.

Here are 2 articles that provide more information and examples of the impact of using distinct in the query


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Good stuff much clearer now. I understood that Distinct would ignore duplicates I thought it might take up more processing time therefore would there be an automatic slow down or speed up if I chose to use it. I guess that depends solely on the amount of records really. If you have a million records and many of them are duplicates. SQL still has to look throught the entire list doesn't it.....?
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

It has to look through the entire list of the selected rows.

>>I guess that depends solely on the amount of records really.
not quite right, it is also affected by the number of columns, and the data types of these.

Distinct is a "row operator" so it compares all columns of each row to arrive at "distinctness." It can be used well, or it can be used badly. Arriving at a distinct list of a single indexed column is way different to arriving at a distinct list of 50 nvarchar columns of various widths (some of which probably aren't indexed).

Below are some viewpoints I share with others:

DISTINCT is *usually* bad

A good rule of thumb -- if you need a distinct list of values in a single column or two, DISTINCT is the way to go.  But if your results "don't look right" or you see some duplicate rows and can't figure out why, do NOT just add DISTINCT to your SELECT to "fix it" !!  Step back, look at your joins, and re-write your query properly.  Even worse, I've seen people simply add DISTINCT to all their SELECT's right from the start, to preemptively "avoid duplicates".  Not good.  ...
also: Why I Hate DISTINCT
by the way, there is no definitive answer to your original question, I hope that is clear.

It will depend on the circumstances of each individual stored procedure
- because there can be good uses of distinct, and there can be bad uses of distinct
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.