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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE tempdb CREATE TABLE dbo.BadMatherFraction (Amount1 INT NOT NULL, Amount2 INT NOT NULL) INSERT dbo.BadMatherFraction WITH (TABLOCK) ( Amount1, Amount2 ) SELECT TOP 10000 x.Amount1, x.Amount2 FROM ( SELECT ABS(CHECKSUM(NEWID())) % 10 AS Amount1, ABS(CHECKSUM(NEWID())) % 10 AS Amount2 FROM sys.messages AS m CROSS JOIN sys.messages AS m2 ) x; |
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?
This will cause problems!
Query-ending problems. Literally. Termination. Error. Red text. Red text everywhere.
|
1 2 3 4 5 |
SELECT (bmf.Amount1 / bmf.Amount2) FROM dbo.BadMatherFraction AS bmf Msg 8134, Level 16, State 1, Line 24 Divide by zero error encountered. |
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?
|
1 2 3 |
SELECT NULLIF(bmf.Amount1, 0) / NULLIF(bmf.Amount2, 0) AS [Perfectly Average] FROM dbo.BadMatherFraction AS bmf ORDER BY [Perfectly Average] |
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.
|
1 2 3 4 5 |
SELECT ISNULL( NULLIF(bmf.Amount1, 0) / NULLIF(bmf.Amount2, 0) , 0) AS [Perfectly Average] FROM dbo.BadMatherFraction AS bmf ORDER BY [Perfectly Average] |
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.
|
1 2 3 4 5 6 7 8 |
SELECT ISNULL( NULLIF(bmf.Amount1, 0) / NULLIF(bmf.Amount2, 0) , 'N/A') AS [Perfectly Average] FROM dbo.BadMatherFraction AS bmf ORDER BY [Perfectly Average] Msg 245, Level 16, State 1, Line 37 Conversion failed when converting the varchar value 'N/A' to data type int. |
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.
|
1 2 3 4 5 |
SELECT ISNULL( CONVERT(VARCHAR(100), NULLIF(bmf.Amount1, 0) / NULLIF(bmf.Amount2, 0) ) , 'N/A') AS [Perfectly Average] FROM dbo.BadMatherFraction AS bmf ORDER BY [Perfectly Average] |
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!
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)
You’re right, I’m just overly protective 🙂
Ah cool. 🙂
The nullif trick is always quicker than writing the long-winded case when 0 approach.
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.
Read the rest of the comments to find out!
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.
Hey buddy, nice email address!
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.
John – a scalar function, eh? Can you think of any drawbacks to using a scalar function in your query?
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