"distinct" keyword use in sql queries

[?scar Martins]

Hi!

Is there any kind of worry when using the keyword "distinct" in sql
commands for querying DB Sqlserver 8??
Some people higher experienced than me in vb.net apps development told
me not to use "distinct" but they mentioned no reason for that...
I´m struggling to not use "distinct"... Is that worth??

thanks in advance.

[Colin Mackay]

I'd find out from them why. I cannot think of any reasons why "distinct"
is dangerous in some way.

IME, I've found that some people have usually had a bad experience with
doing something some particular way and won't do anything in that way
again and try to warn others away from it in a blind manner rather than
just finding out why something went wrong in the first place.

What was their proposal for the way you should be writing the same query
without using the distinct keyword?

[Roji. P. Thomas]

By Specifying DISTINCT you are asking SQL Server to eliminate
duplicates. To Eliminate duplicates the output needs to be sorted.
and Sorting is a costly operation.

Try analysing the execution plan of a query with and without the distinct
clause.

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com

"?scar Martins" <subd...@hotmail.com> wrote in message
news:229f0a23.04080...@posting.google.com...

[Hugo Kornelis]

Hi ?scar,

The one reason to avoid using DISTINCT when it's not needed is that it
decreases the query's performance. The usual technique for executing a
DISTINCT is sorting the intermediate results and then removing rows that
are equal to the row immediately before it. There are other techniques
that SQL Server's query optimizer will use if they appear to be cheaper,
but at the end of the day, looking for and filtering out duplicates will
always require extra work to be carried out by the DB. The performance hit
may vary between queries, but there will always be SOME impact.

Of course, if a query CAN give unwanted duplicates, you will need to
include DISTINCT or use another technique to get rid of duplicates, but I
agree that slapping DISTINCT onto every query "just in case" is bad
practice.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[fdde]

I remember that I could not write to a view that was created using the
'Distinct' clause. That's the only drawback I can think of.
vince
"Colin Mackay" <colma...@yahoo.com> wrote in message
news:eNMudv5...@TK2MSFTNGP09.phx.gbl...

[David Portas]

There's nothing fundamentally wrong with using DISTINCT where it's required.
Unfortunately DISTINCT is often used naively by the inexperienced to
eliminate duplicates produced by ill-formed joins. For that reason, DISTINCT
is often regarded with suspicion because it may just be concealing sloppy or
even faulty code but this is a problem with usage rather than the DISTINCT
modifier itself.

DISTINCT usually forces a sort on the results so it's wise to avoid it where
it isn't necessary. It is also sometimes misunderstood that DISTINCT applies
to the whole result set. Sometimes people struggle to get the result they
want using DISTINCT when GROUP BY would give an easier solution.

--
David Portas
SQL Server MVP
--

[Wayne Snyder]

In addition to all of the others, Distinct performance has been improved in
SQL 2k dramatically.... The server can do a sort, or use hash tables to
eliminate duplicates.... If you need to eliminate duplicates, distinct is
the way to do it..

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

"?scar Martins" <subd...@hotmail.com> wrote in message
news:229f0a23.04080...@posting.google.com...

[Cowboy (Gregory A. Beamer) [MVP]]

DISTINCT is less efficient than some means of filtering. If you can filter
through your WHERE clause, I would aim there first. Other than that,
DISTINCT is a good way of filtering out dupes in a query. If you are never
going to have dupes, do not use it.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

"?scar Martins" <subd...@hotmail.com> wrote in message
news:229f0a23.04080...@posting.google.com...

[Aaron [SQL Server MVP]]

> Unfortunately DISTINCT is often used naively by the inexperienced to
> eliminate duplicates produced by ill-formed joins.

Or bad design that allows duplicates.

--
http://www.aspfaq.com/
(Reverse address to reply.)