Divide by zero error encountered

What it means

Somewhere in the statement, a value was divided by zero, with / or with the modulo operator %. SQL Server doesn’t return infinity or NULL for that by default; it raises error 8134 and stops the statement. Rows already sent to you stay sent, but an INSERT, UPDATE or DELETE that hit it changes nothing. The message never says which expression or row, so look for every division in the query.

It happens for int, decimal and float division alike.

Common causes

1. Ratios and percentages over data with zeros: clicks per view, revenue per order, margin on a zero price.
2. Dividing by an aggregate that can be zero: SUM(x) / COUNT(y) for a group where COUNT(y) is 0, or a SUM of values that cancel out.
3. A divisor that’s usually set: a new row, a test record, or a backfill that left 0 in a column that real data never has 0 in.
4. A per-row calculation in a view that works until one row has a zero.

How to fix it

Divide by NULLIF(divisor, 0)

NULLIF(y, 0) returns NULL when y is 0, and dividing by NULL gives NULL rather than an error:

SELECT id, clicks * 100.0 / NULLIF(views, 0) AS ctr
FROM dbo.campaigns;

If the result must be a number, choose what zero views should mean and say so with COALESCE:

SELECT id, COALESCE(clicks * 100.0 / NULLIF(views, 0), 0) AS ctr
FROM dbo.campaigns;

A CASE does the same and reads well when the rule is more involved:

SELECT id, CASE WHEN views = 0 THEN NULL ELSE clicks * 100.0 / views END AS ctr
FROM dbo.campaigns;

Filter out the zero rows

If rows with a zero divisor shouldn’t be in the result at all, filter them with WHERE views <> 0. That protected the division in every form tried below, but SQL Server doesn’t promise to apply a filter before the other expressions in the query (the same pattern does fail for a conversion), so keep NULLIF in the division as well.

Watch integer division

Not an error, but often found next to one: int / int is integer division, so 5 / 2 is 2. Multiply by 100.0 or CAST one side to decimal first, as above.

What the session settings change

Two SET options decide what a divide by zero does, and XACT_ABORT decides how much is undone:

Don’t turn the warnings off to get NULLs: ANSI_WARNINGS OFF also lets too-long strings be truncated silently, and writes to tables with indexed views or indexes on computed columns fail. Use NULLIF.

ARITHABORT has no effect on errors while ANSI_WARNINGS is on, but it’s part of how SQL Server caches plans: SQL Server Management Studio turns it on and most application drivers leave it off, so the same query can get different plans in each. That’s why a query can be slow in your application and fast in SSMS.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd (which leaves ANSI_WARNINGS on and ARITHABORT off), a table campaigns with views of 1000, 0 and 200:

SELECT id, clicks * 100.0 / views AS ctr FROM seo_sqlerr_data.campaigns;

id ctr
-- ---
1 3.000000000000
Msg 8134, Level 16, State 1, Server 7732422b7f56, Line 1
Divide by zero error encountered.

The first row came back before the second row failed. With NULLIF(views, 0):

id ctr
-- ---
1 3.000000000000
2 NULL
3 2.500000000000

SELECT 1 / 0, 1.0 / 0, CAST(1 AS float) / 0 and 10 % 0 all gave the same error. WHERE views <> 0 AND clicks * 100.0 / views > 1, the same conditions the other way round, and the filter in a derived table all returned rows 1 and 3 without an error. With each combination of settings, the statement SET @r = 1 / 0 inside a transaction, followed by an insert into a log table and a COMMIT:

• ANSI_WARNINGS ON, with ARITHABORT on or off: 8134, then the insert and the commit ran.
• ANSI_WARNINGS OFF, ARITHABORT ON: 8134, nothing after it ran, and @@TRANCOUNT was 0 in the next batch.
• Both off: @r was NULL, the batch carried on, and sqlcmd -m-1 showed Msg 3607, Level 0, State 1 … Division by zero occurred.
• XACT_ABORT ON: 8134, and the transaction was rolled back.

SQL Server 2019 and 2025 printed the same message.

In Inlet

When SQL Server refuses, Inlet shows its message with SQL Server error 8134, state 1, severity 16 and links to this page. Query tabs run T-SQL a batch at a time and show every result set and message a batch returns, so you see the rows that came back before the error as well as the error itself.