On this page
Why it happens
Error 8134 means at least one row in your query tried to divide by zero (or take a modulo by zero). SQL Server evaluates the arithmetic row by row, so it only needs a single row where the divisor is 0 to abort the whole statement with this message.
The classic trigger is a ratio like Revenue / Units where a row has Units = 0, an average computed as Total / Count when the count is zero, or a percentage calculation whose base is zero. A divisor that started as NULL and was turned into zero by COALESCE or ISNULL can also cause it.
The error is severity 16, a normal, catchable expression error rather than a server problem. It stops the batch and rolls back the current statement, so you get no rows back until you guard the divisor.
Examples
A ratio where the divisor is zero
CREATE TABLE dbo.Sales (
product NVARCHAR(50),
revenue DECIMAL(10,2),
units INT
);
INSERT INTO dbo.Sales (product, revenue, units)
VALUES ('Widget', 100.00, 4),
('Gadget', 50.00, 0); -- units is 0 for this row
SELECT product, revenue / units AS price_per_unit
FROM dbo.Sales;Msg 8134, Level 16, State 1, Line 12 Divide by zero error encountered.
An average with a zero count
-- total is 0 and count is 0, so the division fails
DECLARE @total INT = 0;
DECLARE @count INT = 0;
SELECT @total / @count AS average;Msg 8134, Level 16, State 1, Line 5 Divide by zero error encountered.
A modulo by zero
-- the % operator fails on a zero divisor too
SELECT 10 % 0 AS remainder;Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered.
How to fix it
Guard the divisor with NULLIF
Wrap the divisor in NULLIF(divisor, 0). When the divisor is zero, NULLIF turns it into NULL, and dividing by NULL yields NULL instead of raising error 8134. This is the clean, standard fix.
SELECT product,
revenue / NULLIF(units, 0) AS price_per_unit
FROM dbo.Sales;Show 0 instead of NULL with COALESCE
NULLIF makes the result NULL for the zero-divisor rows. If you would rather display 0 (or any default), wrap the whole expression in COALESCE or ISNULL.
SELECT product,
COALESCE(revenue / NULLIF(units, 0), 0) AS price_per_unit
FROM dbo.Sales;Handle the zero explicitly with CASE
If you want full control over what happens when the divisor is zero, use a CASE expression to test for it before dividing.
SELECT product,
CASE WHEN units = 0 THEN NULL
ELSE revenue / units
END AS price_per_unit
FROM dbo.Sales;Control behavior with ARITHABORT and ANSI_WARNINGS
Session settings decide whether a divide by zero aborts or returns NULL with a warning. Turning both off lets the expression yield NULL instead of erroring, but this is fragile and affects the whole batch, so prefer NULLIF for a targeted fix.
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
SELECT product, revenue / units AS price_per_unit
FROM dbo.Sales; -- returns NULL for units = 0 with a warningHow to prevent it
Always wrap user-driven or data-driven divisors in NULLIF(divisor, 0) whenever a value can legitimately be zero, such as counts, quantities, or denominators from user input. Combine it with COALESCE when you need a friendly default like 0.
Validate denominators before they reach the query: reject or clean zero and NULL counts in the application, and remember that ISNULL(x, 0) can silently create a zero divisor. If you are also fighting type or overflow issues in the same expression, see error 8115 for arithmetic overflow.
Common questions
Why does NULLIF fix the divide by zero error?
NULLIF(divisor, 0) compares the divisor to zero and returns NULL when they are equal, otherwise it returns the divisor unchanged. Dividing any number by NULL produces NULL rather than raising error 8134, so the row that would have failed now simply returns an unknown result and the query completes.
Should I return NULL or 0 when the divisor is zero?
It depends on the meaning of your data. NULL says the ratio is undefined or unknown, which is honest when there is no denominator to divide by. Returning 0 (via COALESCE or ISNULL) is convenient for reports and charts that cannot handle NULL, but it can be misleading because a zero result is not the same as no result. Choose NULL for correctness and 0 for display.
Does error 8134 also happen with the modulo operator?
Yes. The modulo operator % divides internally to find the remainder, so 10 % 0 raises the same error 8134. Guard it the same way with NULLIF, for example 10 % NULLIF(@n, 0).