Divide By Zero Hero

Alright, so at least they’re not NULLs

But zero is actually literally much more problemaaaaatic per se (add lots of annoying vocal fry to this when you read it) when it comes to math. Or maths, depending on which message board you read. Zero has a weird history. It wasn’t always a number, and it can be a difficult concept in cases.

After all, what is zero? Is it actually zero? Is it my favorite Jami Gertz movie and third favorite Bret Easton Ellis book “Less Than Zero“?

Probably not

But we also don’t need to have that deep a discussion about zero, or my favorite books and movies. We just need to avoid this error:

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

Why? Because it’s just as nebulous and unhelpful as certain other error messages, but thankfully it’s a bit easier to root out. After all, you just need to look at columns taking part in some division arithmetic.

Method to the sadness

There are several options for fixing this. For instance, you can use a CASE expression, or COALESCE, but I find they get a tad muddled to write after a while, especially if we’re safeguarding multiple columns from our mathematical disaster. Plus, under the covers, the functions I like to use are just case expressions anyway. Isn’t it nice that SQL Server will save you a touch of typing? I think so. What a considerate piece of software!

This is a bit of a beginner tip, but it came up while we were at DBA Days, so I figured I’d write about it in case anyone runs across it.

Let’s get ourselves a table!

It has some numbers in it! More precisely, it has 10,000 random rows of the numbers 0-9 in each column. Precisely random.

See?

Zeros and zeros and zeros

Zeros and zeros and zeros

This will cause problems!

Query-ending problems. Literally. Termination. Error. Red text. Red text everywhere.

To fix it, you can use a built-in function called NULLIF, which will return a NULL if the first value you pass in tests true to the second value. Words. Words are hard. Queries are easy, right?

This works, but then we have a couple thousand rows of NULL being sent back at the beginning of our results. This may confuse people. How can math be NULL? Where did my life go? Am I getting enough fiber?

Well, uh. Remember our other NULL handling function? ISNULL? We can wrap the whole expression in that.

This returns a zero for any columns that return NULL, which will happen when you try to divide any number by NULL. A lot of people will be tempted to replace NULLS with ‘N/A’, or something similar, to denote that the math couldn’t be applied, but you can run into type conversion errors when you do that.

This is because the column starts typed as an integer, and SQL can’t gracefully convert ‘N/A’ into an integer. No way, no how. I know that sounds annoying, but believe me, it’s much better than columns changing datatypes on you mid-flight. That’d really screw everything up! If you’re dead set on doing this, you need to add in a bit more code.

This will run without error, and so long as you don’t need to do anything integer-y with the results, probably won’t harm anything.

Probably.

Thanks for reading!

,
Previous Post
Are You Load Testing for Best Case Scenario, or Worst Case?
Next Post
[Video] Office Hours 2016/09/28 (With Transcriptions)

20 Comments. Leave new

  • Normally, I use the following approach

    CASE bmf.Amount2 when 0 then 0 else (bmf.Amount1 / bmf.Amount2) end as SomeColumn

    I think it is more readable, you can of course instead of 0 return anything you want but you might need to convert to varchar as well

    Denis

    • Yep, isnull(nullif()) is my threshold for just rewriting as a case statement.

      In this particular scenario though I’d almost certainly just divide by ifnull(bmf.amount2, 0) to get a null result and handle it at a presentation layer if there was one. If you’ve got code that divides a value by another value whose permanently-stored column may have zeroes in it (outside of a staging layer) then that would flag to me that that column may not be being populated or accessed correctly. It’s entirely possible that it is valid, but might just as well indicate that 0 is a placeholder value or that the appropriate rows should be being filtered out earlier — unfortunately on the latter I’ve run into Koen’s issue below quite a few times; I’m only just beginning to naturally code for it in advance.

  • What’s annoying sometimes is when you filter out the offending rows in the WHERE clause, but still get division by zero errors because the optimizer decides to do the arithmetic before the filtering.

    • Oh, yeah. I read about that in an internals book.

      It’s technically called a deferred “screw you, pal”.

  • Or you can write the ‘ChuckNorris’ function – because he can divide by zero.

    And, he’s counted to infinity.

    Twice.

  • Is it necessary that the numerator has to be wrapped in a NULLIF ? I think just putting the divisor in a NULLIF suffices. No need to evaluate the numerator with it. You can do this and get the same results:

    SELECT ISNULL(X/NULLIF(y,0),0)

  • The old Unisys 1100 mainframes used EBCDIC and ones complement so they actually had such a beast as a positive or negative zero. Never worked on one but it’s worth a laugh to point it out. It also had to be tested for as a “case”. (Two’s compliment is used these days so it isn’t a concept there.)
    I didn’t believe it myself as it predates moi and had to look it up.
    http://www.fourmilab.ch/documents/univac/
    AND:
    http://www.fourmilab.ch/documents/univac/minuszero.html

    I’m sure you’re sorry you didn’t ask.

  • Excellent post and great comments so far. I love reading your newsletter after work – helps a lot to bring me back to normal 😉

  • It should also be stated that NULL value could be handled on client side.
    Sometime, it’s better that way!
    Also, it’s ok if the numerator is zero because the result will be zero anyway. So there is no need to replace it by NULL to then replace NULL by zero.

    SELECT MyResult = bmf.Amount1 / NULLIF(bmf.Amount2, 0)

  • Either coalesce or case are good choices but one of downfalls I have found of both is their ability to ignore indexes when used, may when the battle but lose the war

  • Why NULLIF(bmf.Amount1, 0) / NULLIF(bmf.Amount2, 0) and not simply bmf.Amount1 / NULLIF(bmf.Amount2, 0)? Dividing zero is perfectly fine, it’s dividing *by* zero that’s problematic.

  • good stuff. wrapping a divisor (only) to avoid the dreaded divide-by-zero error is my personal A#1 use case for the NULLIF function.

    another quick-n-dirty if you’re rushed, dealing with denominators that are either 0 or large positive numbers, numerators that are (mostly) smaller, and can tolerate a little fuzz in the results, is to simply add 1 to the denominator. i don’t do that, at all, ever (really, i don’t), but i saw a situation where it kinda made sense once and thought it was kinda clever.

  • The article begins with a discussion of what is zero, but only describes how to handle divide-by-zero errors. We shouldn’t forget what the application (or user) is trying to achieve. Often, simply replacing an illegal divide-by-zero with a zero is fine. Occasionally the assumption will be wrong and will misrepresent the data. Your SQL statement will return zero but in reality you mean unknown.

  • I use isZero (http://www.bennadel.com/blog/473-sql-iszero-and-nullif-for-dividing-by-zero.htm) function to solve divide by zero problem.

    SELECT isNull((100 / dbo.isZero(0,null)),0)

    Simply converting 0 to null, then the expression will return null, and lastly convert null to 0 with isNull.
    Simple to write, simple to understand.

  • I had run the query and got too many N/A than were expected because the nullif function is running on bmf.Amount1 while it should be running on bmf.Amount2 only

Menu
{"cart_token":"","hash":"","cart_data":""}