Here are five options for dealing with error Msg 8134 “Divide by zero error encountered” in SQL Server.

The Error

First, here’s an example of code that produces the error we’re talking about:

SELECT 1 / 0;

Result:

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

We get the error because we’re trying to divide a number by zero. Mathematically, this does not make any sense. You can’t divide a number by zero and expect a meaningful result.

To deal with this error, we need to decide what should be returned when we try to divide by zero. For example, we might want a null value to be returned. Or we might want zero to be returned. Or some other value.

Below are some options for dealing with this error.

Option 1: The NULLIF() Expression

A quick and easy way to deal with this error is to use the NULLIF() expression:

SELECT 1 / NULLIF( 0, 0 );

Result:

NULL

NULLIF() returns NULL if the two specified expressions are the same value. It returns the first expression if the two expressions are different. Therefore, if we use zero as the second expression, we will get a null value whenever the first expression is zero. Dividing a number by NULL results in NULL.

Actually, SQL Server already returns NULL on a divide-by-zero error, but in most cases we don’t see this, due to our ARITHABORT and ANSI_WARNINGS settings (more on this later).

Option 2: Add the ISNULL() Function

In some cases, you might prefer to return a value other than NULL.

In such cases, you can pass the previous example to the ISNULL() function:

SELECT ISNULL(1 / NULLIF( 0, 0 ), 0);

Result:

0

Here I specified that zero should be returned whenever the result is NULL.

Be careful though. In some cases, returning zero might be inappropriate. For example, if you’re dealing with inventory supplies, specifying zero might imply that there are zero products, which might not be the case.

Option 3: Use a CASE Statement

Another way to do it is to use a CASE statement:

DECLARE @n1 INT = 20;
DECLARE @n2 INT = 0;

SELECT CASE
    WHEN @n2 = 0
    THEN NULL
    ELSE @n1 / @n2
END

Result:

NULL

Option 4: The SET ARITHABORT Statement

The SET ARITHABORT statement ends a query when an overflow or divide-by-zero error occurs during query execution. We can use it in conjunction with SET ANSI WARNINGS to return NULL whenever the divide-by-zero error might occur:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
SELECT 20 / 0;

Result:

NULL

Microsoft recommends that you always set ARITHABORT to ON in your logon sessions, and that setting it to OFF can negatively impact query optimisation, leading to performance issues.

Some clients (such as SQL Server Management Studio) set ARITHABORT to ON by default. This is why you probably don’t see the NULL value being returned when you divide by zero. You can use SET ARITHIGNORE to change this behaviour if you prefer.

Option 5: The SET ARITHIGNORE Statement

The SET ARITHIGNORE statement controls whether error messages are returned from overflow or divide-by-zero errors during a query:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

SET ARITHIGNORE ON;
SELECT 1 / 0 AS Result_1;

SET ARITHIGNORE OFF;
SELECT 1 / 0 AS Result_2;

Result:

Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
+------------+
| Result_1   |
|------------|
| NULL       |
+------------+
(1 row affected)
Commands completed successfully.
+------------+
| Result_2   |
|------------|
| NULL       |
+------------+
Division by zero occurred.

Here, I set ARITHABORT and ANSI_WARNINGS to OFF so that the statement wasn’t aborted due to the error, and NULL is returned whenever there’s a divide-by-zero error.

Note that the SET ARITHIGNORE setting only controls whether an error message is returned. SQL Server returns a NULL in a calculation involving an overflow or divide-by-zero error, regardless of this setting.

In the above example we can see that when ARITHIGNORE is ON, the division by zero error is not returned. When it’s OFF, the division by zero error message is returned.