What is the Distinct Operator?

You've probably used it, but do you know what goes on when you use the DISTINCT keyword? Or what a HASH MATCH aggregate is? Join SQL MVP Grant Fritchey for a quick two minute introduction to this great query plan operator.

I'm currently working for FM Global, an industry leading engineering & insurance company, as a DBA. I've done development of large scale applications in languages such as VB, C# and Java. I've worked in SQL Server from the hoary days of 6.0. My nickname at work is the "The Scary DBA." I even have an official name plate with it. I wear it proudly. I was awarded a Microsoft MVP in April of '09.

Venkata Taduri on 2/24/2009

Pretty informative. Thank you.

Jamshid Nouri on 6/25/2009

short and informative demo

Santosh Sharma on 6/25/2009

Though we know lot of operator this and other video gives us a better understanding of the concept and how they are executed in the background. Thank you Sir.

Lou Gallo on 6/25/2009

Sorry, in my mind, the description didn't match the title of this item. Interesting background though.

Grant Fritchey on 6/25/2009

Glad the video was helpful. These short videos seem to be pretty hit or miss.

Jed Perlowin on 6/25/2009

I was hoping he would compare it to the GROUP BY to see what the differences (if any) are.

Ivan on 6/25/2009

Thnks

datamama on 6/25/2009

nicely presented - BUT - a comparison to execution plan for a GROUP BY on the same data would have been quite useful

Carla Wilson on 6/25/2009

Ah, grasshopper! A good teacher shows the path but lets the student explore! Ok, so I set up a query using "group by" instead of "distinct", and I got the exact same query plan.

Carla Wilson on 6/25/2009

er, I should say, execution plan.

pundrick on 6/25/2009

Concise and complete

Grant Fritchey on 6/26/2009

I'm not sure who is the teacher and who is the student in this situation. ;-) That is interesting that it showed an identical execution plan. What I hoped was the key take-away here is that the DISTINCT operation is an aggregation and therefor, usually expensive.

Girish kalwadia on 6/28/2009

Its realy good and helpfull. Thanks for that

325BD96788 on 7/6/2009

Simple but good...

325BD96788 on 7/6/2009

Simple but good...

Hansjörg on 7/8/2009

but what if no index covers the columns in the distinct list?

Caleb Bell on 7/16/2009

Is there a way to optimize usage of the DISTINCT operator (i.e. is there a way to minimize the cost of the Hash Match (Aggregate) function)?

Grant Fritchey on 7/17/2009

Since it's an aggregation, it really depends on what you're aggregating. If you have a covering index you could see some performance improvement. But in general, aggregation is expensive and frequently involves extra operations such as the Hash Match in order to arrive at the unique list of data.

marcor on 8/13/2009

It's excellent. Thanks.