Arithmetic overflow error converting expression to data type int

What it means

A value came out larger (or more negative) than the data type holding it allows, so SQL Server stopped the statement. An INSERT or UPDATE that hits it changes nothing.

Msg 8115, Level 16, State 2, Server 7732422b7f56, Line 1
Arithmetic overflow error converting expression to data type int.

The end of the message tells you what overflowed:

The limits: int is ±2,147,483,647, bigint ±9,223,372,036,854,775,807, and decimal(5, 2) holds up to 999.99. Calculating DATEDIFF in small units over a long span fails similarly, as error 535 (The datediff function resulted in an overflow.).

Common causes

1. SUM, AVG or COUNT over int. SUM and AVG of an int column return int, and COUNT returns int, so a large table’s total overflows even though every row fits.
2. Arithmetic on two int values: quantity * unit_price_cents, @x + 1. The result’s type comes from the operands, not from the column you’re saving it into.
3. A decimal column too small for the data: a price of 1234.50 into decimal(5, 2).
4. An int identity that ran out. After about 2.1 billion inserts (or fewer, if many failed or rolled back, since those use up values too), every insert fails.
5. A cast into a smaller type: CAST(x AS tinyint) with x over 255 (error 220).

How to fix it

Do the arithmetic in a bigger type

Cast one operand before the calculation, not the result after it:

SELECT SUM(CAST(amount AS bigint)) AS total FROM dbo.payments;
SELECT COUNT_BIG(*) AS rows_in_table FROM dbo.events;
SELECT CAST(quantity AS bigint) * unit_price_cents AS line_total FROM dbo.order_lines;
SELECT DATEDIFF_BIG(millisecond, started_at, finished_at) AS ms FROM dbo.jobs;

For money and other decimals, cast to a decimal with enough digits, such as decimal(19, 4).

Widen the column

ALTER TABLE dbo.prices ALTER COLUMN amount decimal(12, 2) NOT NULL;

Repeat the column’s NULL or NOT NULL, or it becomes nullable. If an index, key or constraint uses the column, SQL Server refuses with error 5074 until you drop it (and re-create it after).

Check how much of each IDENTITY is used

SELECT OBJECT_SCHEMA_NAME(ic.object_id) + N'.' + OBJECT_NAME(ic.object_id) AS table_name,
       TYPE_NAME(ic.system_type_id) AS type,
       CAST(ic.last_value AS bigint) AS last_value,
       CAST(CAST(ic.last_value AS bigint) * 100.0 /
            CASE TYPE_NAME(ic.system_type_id) WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 32767
                 WHEN 'int' THEN 2147483647 ELSE 9223372036854775807 END AS decimal(5, 1)) AS pct_used
FROM sys.identity_columns AS ic
WHERE ic.last_value IS NOT NULL
ORDER BY pct_used DESC;

Anything above about 80% needs a plan before it fills.

Move an exhausted id to bigint

The column is almost always the primary key, so the key has to go first, along with every foreign key that references it (whose columns need to become bigint too):

ALTER TABLE dbo.events DROP CONSTRAINT PK_events;
ALTER TABLE dbo.events ALTER COLUMN id bigint NOT NULL;
ALTER TABLE dbo.events ADD CONSTRAINT PK_events PRIMARY KEY (id);

On a large table this rewrites every row and blocks the table while it runs, so test it on a copy and plan the time. As a stopgap while you prepare, an int identity can restart from the negative end of its range, if nothing depends on ids being positive or increasing:

DBCC CHECKIDENT ('dbo.events', RESEED, -2147483648);

Get NULL instead of an error

TRY_CAST and TRY_CONVERT return NULL when the value doesn’t fit, which suits cleaning imported data. Don’t use it to hide overflows in totals.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, each statement in its own batch:

SELECT SUM(amount) FROM (VALUES (2000000000), (2000000000)) AS v (amount);
SELECT CAST(123.456 AS decimal(4, 2));
SELECT CAST('99999' AS decimal(4, 1));
SELECT CAST(1000 AS tinyint);
SELECT DATEDIFF(millisecond, '2026-01-01', '2026-10-11');

Msg 8115, Level 16, State 2, Server 7732422b7f56, Line 1
Arithmetic overflow error converting expression to data type int.
Msg 8115, Level 16, State 8, Server 7732422b7f56, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Msg 8115, Level 16, State 8, Server 7732422b7f56, Line 1
Arithmetic overflow error converting varchar to data type numeric.
Msg 220, Level 16, State 2, Server 7732422b7f56, Line 1
Arithmetic overflow error for data type tinyint, value = 1000.
Msg 535, Level 16, State 1, Server 7732422b7f56, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

SELECT 2147483647 + 1, AVG over the same two values, and COUNT(*) over a cross join of about seven billion rows gave the same data type int message. SUM(CAST(amount AS bigint)) returned 4000000000, and DATEDIFF_BIG returned 24451200000. A table with id int IDENTITY(2147483646, 1) took two rows, and the third insert failed:

Msg 8115, Level 16, State 1, Server 7732422b7f56, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

ALTER COLUMN id bigint failed with 5074 on PK_events; dropping the key, altering the column and re-adding the key worked, and the next insert got 2147483648 (in a transaction we rolled back). After RESEED, -2147483648 the next id was -2147483647. Inserting 1234.5 into a decimal(5, 2) column gave the numeric to data type numeric message. SQL Server 2019 and 2025 printed the same messages.

In Inlet

The structure designer writes the ALTER COLUMN for a new type or size, shows the T-SQL first, and warns that a type change rewrites or scans the table while locking it. Activity lists tables’ sizes and use. When SQL Server refuses, Inlet shows its message with SQL Server error 8115, state 2, severity 16 and links to this page.