SQL query - cost of using DISTINCT

Question

Does the use of DISTINCT have a lot of influence on the query cost of a SQL query's execution plan?

On a table with 100,000 rows, what difference will there be between:

select * from TABLE

and 

select distinct(*) from TABLE 

in term of milliseconds? 

Answer 1

Generally, the answer is many milliseconds. The select distinct is equivalent to doing a group by on all the columns. It is unlikely that you have an index on all the columns, so an index will not generally be very useful.

Some database engines might be smart enough to detect if one of the columns is declared distinct or a primary key. In that case, there might be an engine that optimizes the query.

But, in general, you should avoid using select distinct unless you need to.