In the real world, everyone knows that if you divide by zero a wormhole will open up and swallow the universe. In SQL Server, it’s not good, but it’s not nearly as dramatic. I encountered the following error this week and thought it would make a good topic:
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.
There are multiple ways to handle this error message in SQL Server and some are better than others. Let’s take a look at a few.
First, we’ll create a quick script to reproduce the error:
DECLARE @INT0 AS INT = 0 DECLARE @INT1 AS INT = 1 SELECT @INT1/@INT0; GO
The first way we can avoid the divide by zero error message is to use NULLIF. In our example, we can wrap the divisor in NULLIF telling SQL Server to return a NULL value if @INT0 is equal to 0. If we run the following, we’ll get a result of NULL:
DECLARE @INT0 AS INT = 0 DECLARE @INT1 AS INT = 1 SELECT @INT1/NULLIF(@INT0,0); GO
We can also use a CASE statement to only divide if a value is not 0. Below, we’ll check on the value of @INT0 and return NULL since it is set to 0. If this value was not 0, then we would proceed with the division:
DECLARE @INT0 AS INT = 0 DECLARE @INT1 AS INT = 1 SELECT CASE WHEN @INT0 = 0 THEN NULL ELSE @INT1 / @INT0 END; GO
SET ARITHABORT / ANSI_WARNINGS OFF
This is the way I ended up getting by the error message last time I encountered it. I was running more of an ad hoc script to pull information. Without making a change to the query, I added SET ARITHABORT OFF and SET ANSI_WARNINGS OFF to the top of the script before running:
SET ARITHABORT OFF; SET ANSI_WARNINGS OFF; DECLARE @INT0 AS INT = 0 DECLARE @INT1 AS INT = 1 SELECT @INT1/@INT0; GO
This isn’t the best way or really even a good way to rely on avoiding “Divide by Zero” errors on a permanent basis.
On Microsoft’s “SET ARITHABORT” remarks, they make it clear that setting ARITHABORT OFF is not recommended. Making the change can cause performance issues which can then be more difficult to troubleshoot. They reference the always frustrating “fast in SSMS but slow in the app” problem. We’ll try to avoid that one.
Two Out of Three Ain’t Bad
I hope the quick overview helps if you run into a divide by zero error of your own. Either NULLIF or a CASE statement is the way to go.
Thanks for reading!