So, your query looks something like this:

SELECT s.Quantity / t.TotalQuantity
FROM Sales s
INNER JOIN Totals t
ON t.ProductNumber = s.ProductNumber
WHERE s.SalesDate >= '1/1/2012'
AND t.TotalQuantity <> 0

Totally safe, right? That check on non-zero TotalQuality values should prevent divide-by-zero errors?

Then you run it in production against 100,00 rows and BLAM!

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Ye Gods!  How could this possibly happen?!? Thousands of years of mathematics and physics turned on its head from one simple SELECT statement!

I’ll tell you how it happens… SQL Server has to break this query into a step-by-step plan… and, in its nearly infinite wisdom, it may decide to do things in an order you did not expect.

For example, it may decide to filter out the SalesDate part first, then do all the joins, and then the division and THEN filter on the TotalQuality clause.  If  this is the plan it chooses, and you have zeroes for TotalQuantity… BLAMMO!  You just got yourself a divide-by-zero before you even got to the last filter step which would have saved you.

The ONLY way to guarantee this doesn’t happen is to use a CASE statement in your SELECT as follows. It’s ugly, but safe:

SELECT CASE WHEN t.TotalQuantity = 0 THEN 0
ELSE s.Quantity / t.TotalQuantity
END
FROM Sales s
INNER JOIN Totals t
ON t.ProductNumber = s.ProductNumber
WHERE s.SalesDate >= '1/1/2012'
AND t.TotalQuantity <> 0

Any time you do a division anywhere in your SQL code, you should follow this pattern or risk the wrath of the divide-by-zero gods.