Where Clauses Cannot Prevent Divide-by-Zero Errors

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s