SQL Server error 8134: 'Divide by zero error encountered'

Divide by zero error encountered. (error 8134) means at least one row had zero in the divisor. Wrap the divisor in NULLIF(divisor, 0) so that row gives NULL instead of an error, or use CASE to say what a zero should mean.

The one-word fix is x / NULLIF(y, 0). Use CASE WHEN y = 0 THEN ... ELSE x / y END when the report needs a number such as 0 rather than an empty cell.

The error

SELECT Name, Clicks * 100.0 / Views AS ClickRate
FROM dbo.Campaigns;

Name                                               ClickRate
-------------------------------------------------- ----------------------------
Spring                                                           2.500000000000
Summer                                                           3.000000000000
Msg 8134, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Divide by zero error encountered.

Why it happens

The campaigns table had a third row, Draft, with zero clicks and zero views, and dividing by zero has no answer, so SQL Server stops the statement when it reaches that row. Note what sqlcmd printed: the first two rows had already been sent to the client before the error arrived. An application that reads rows as a stream can therefore show part of a result and then fail, which is easy to mistake for a network or paging problem.

Like error 512, this one depends on the data, not on the query text. A report can run for months and fail on the day a new, empty campaign, product or class is added. Any expression that divides by a count, a total or a user-supplied number is a candidate.

The fix

-- fix A: NULLIF turns a zero divisor into NULL, and x / NULL is NULL
SELECT Name, Clicks * 100.0 / NULLIF(Views, 0) AS ClickRate
FROM dbo.Campaigns
ORDER BY Id;
GO
-- fix B: decide what zero means with CASE
SELECT Name,
       CASE WHEN Views = 0 THEN 0 ELSE Clicks * 100.0 / Views END AS ClickRate
FROM dbo.Campaigns
ORDER BY Id;

Fix A returned all three rows with NULL as the Draft's click rate; fix B returned .000000000000 for it. NULL is usually the honest answer, since a rate with no views is unknown rather than zero, and AVG skips NULLs: the average of fix A's rates came out as 2.75, the mean of Spring and Summer only, so the empty campaign does not drag it down. Pick fix B when a consumer of the result cannot handle NULL. The 100.0 in both queries matters too: written as Clicks * 100 / NULLIF(Views, 0), with whole numbers on both sides, SQL Server did integer division and returned 2 for Spring instead of 2.5.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e8134.sql on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. One campaigns table with three fictional rows, the last with zero views.

Frequently asked

How do I avoid 'Divide by zero error encountered' in SQL Server?

Write the divisor as NULLIF(divisor, 0). When it is zero, NULLIF returns NULL and the division returns NULL instead of raising error 8134.

Should a division by zero return 0 or NULL in SQL?

NULL is usually more honest, because the ratio is unknown, and AVG ignores NULLs. Return 0 with a CASE expression only when whatever reads the result cannot handle NULL.

Why did my query return some rows before the divide by zero error?

SQL Server streams rows to the client as it computes them. Rows before the first zero divisor were already sent when error 8134 stopped the statement.

More decoded errors in the Fixes category.