Handling Divide By Zero Error

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

NULLIF

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

CASE Statement

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!