QuickTip: Reproducible SELECT Results

Oftentimes the most difficult part of debugging is reproducing the behavior:

A physicist, a mechanic, and a programmer were in a car driving over a steep alpine pass when the brakes failed. The car was getting faster and faster, they were struggling to get round the corners and once or twice only the feeble guard rail saved them from crashing down the side of the mountain. They were sure they were all going to die, when suddenly they spotted an escape lane. They pulled into the escape lane, and came safely to a halt.

The physicist said, "We need to model the friction in the brake pads and the resultant temperature rise to see if we can work out why they failed."

The mechanic said, "I think I've got a few wrenches in the back. I'll take a look and see if I can work out what's wrong."

The programmer said, "Why don't we get going again and see if it's reproducible?"

Reproducing SELECT Query Results

An important concept in relational database theory is that records have no inherent order.

If you don't specify a sort order for your records, they may appear to be returned in the same order every time, but there is no guarantee they will appear in the same order every time.

It's a good habit to build your queries so as to guarantee your records appear in the same order every time you execute them.  This makes debugging much easier.  

To do this, you should make it a habit to always include a unique field as part of the sort order.

The easiest way to do this is to throw the unique field in as the last entry in your ORDER BY list.  The field acts as a final tiebreaker to ensure that the records sort in the same order every time.  Here's a couple of quick examples to illustrate the concept:

BAD: Potential Sort Order Ambiguity

SELECT *
FROM Customer
ORDER BY LastName, FirstName;

GOOD: Guaranteed Sort Order Reproducibility

SELECT *
FROM Customer
ORDER BY LastName, FirstName, CustomerID;

Referenced Articles

Reproducing Errors

A physicist, a mechanic, and a programmer were in a car driving over a steep alpine pass when the brakes failed...

No Longer SetMike Wolfe