Use the Index, Luke

While I’m on a T-SQL bent, I stumbled upon (literally, beware of StumbleUpon as a way to waste heroic amounts of time) a free eBook called “Use The Index, Luke”. Its by a chap called Markus Winand, who I freely admit to having never heard of  (and Google isn’t particularly enlightening).

However it’s a work in progress explaining in relatively clear terms what database indexes are, how they work and how they can impact performance. It’s a pretty important topic for anyone who writes SQL – so I’d recommend reading what’s there and tracking it’s progress.

Why to avoid DISTINCT and GROUP BY to get unique records

This is a quick and dirty post on the use of DISTINCT or GROUP BY to get unique records, based on something I helped a developer with over the last couple of weeks.

Their thought process was that because they were getting duplicate records, the easiest way to get rid of them was to slap a DISTINCT at the start of the query to get unique results. Which, in a sense, is OK – because it worked (sort of).However there’s two very good reasons why this is not always a good approach.

#1: Your query is wrong

If you are getting back duplicate records, what it probably means is that you are really doing your query wrong. The below example is an admittedly imperfect example of this – as the first query returns far more than intended – but was close to what I was dealing with:

USE AdventureWorks

/* Query 1: Using DISTINCT to try to eliminate duplicates */

select    DISTINCT
s.Name,
CASE
WHEN sc.ContactTypeID = 11 THEN ‘Y’
ELSE ‘N’
END    AS    ’OwnerContact’
from    Sales.Store s
left join Sales.StoreContact sc
ON s.CustomerID = sc.CustomerID

/* Query 2: Using a properly formed WHERE clause */

select    s.Name,
‘Y’ AS    ’OwnerContact’
from    Sales.Store s
left join Sales.StoreContact sc
ON s.CustomerID = sc.CustomerID
WHERE sc.ContactTypeID = 11

What I’m trying to illustrate with the example above is that if you consider more carefully what records you are bringing back in your joins, you are less likely to end up with duplicates. By making sure you are only joining to tables in such a way as to bring back the data you need is going to reduce the risk of other errors creeping in.

#2: Performance

If you are getting duplicate records, you are bringing back more data than you need. On top of this the DISTINCT or GROUP BY operations are having to go over the whole returned data set to identify unique records. This can get pretty expensive pretty quicky in terms of database operations.

From my perspective badly performing queries are a lesser sin than incorrect ones. I doubt many business users will be making decisions based on query length, but they will on the data you serve up to them.

Summing up

All I want to do in this post is make you pause and think before doing a DISTINCT or GROUP BY purely to eliminate duplicates – especially if you don’t really understand why you are getting them. A better designed and more accurate query can often get rid of the dupes and cut off the risk of bad data in the future.

Update 25 Jul 2011: Mark Caldwell articulates it better than me @ SQL Team Blog: Why I Hate DISTINCT