SQL Server Error 220

Error number

220

Severity

16 (user-correctable error)

State

2 (tinyint) or 1 (smallint) (see the state table below)

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Data Type & Conversion

Last reviewed

23 October 2026

What it means

Error 220 means a whole number did not fit into a tinyint (0 to 255) or smallint (-32768 to 32767) value, and the message shows the type and the exact value that overflowed.

Every integer type has a fixed range. tinyint holds 0 to 255 and cannot be negative; smallint holds -32768 to 32767. When a value is cast, assigned, inserted or calculated into one of these types and falls outside that range, SQL Server stops the statement with 220 at severity 16. It never wraps round or truncates the value.

The message is unusually helpful: it names the target type and prints the value that did not fit, for example value = 33000. Use that number to find the row or the calculation. On SQL Server 2025 the small integer types raise 220, while an overflow into int, bigint or decimal raises error 8115 instead, without the value.

Common causes

• A tinyint or smallint column that was sized for today's data and is now outgrowing it, for example a stock count or a sequence number.
• An UPDATE that adds to a small integer column (stock = stock + 1000) and pushes it past the maximum.
• A negative value, such as a refund or a correction, stored in a tinyint column, which cannot hold anything below 0.
• Imported or user-supplied numbers that are larger than the column was designed for.
• A variable declared as tinyint or smallint as a loop counter or accumulator in a procedure.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name, t.name AS data_type
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
JOIN sys.tables AS tb ON tb.object_id = c.object_id
WHERE t.name IN ('tinyint', 'smallint')
ORDER BY table_name, c.column_id;

SELECT MIN(stock) AS min_stock, MAX(stock) AS max_stock,
       32767 - MAX(stock) AS headroom
FROM dbo.Products;

SELECT id, new_stock
FROM dbo.StockImport
WHERE TRY_CAST(new_stock AS smallint) IS NULL
  AND new_stock IS NOT NULL;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

ALTER TABLE dbo.Products ALTER COLUMN stock INT NULL;

SELECT id, stock, CAST(stock AS INT) + 1000 AS new_stock,
       CASE WHEN TRY_CAST(CAST(stock AS INT) + 1000 AS smallint) IS NULL
            THEN 'would overflow' ELSE 'ok' END AS check_result
FROM dbo.Products;

ALTER TABLE dbo.Products
ADD CONSTRAINT CK_Products_rating CHECK (rating BETWEEN 1 AND 5);

Examples

Casting a value above 255 to tinyint

SELECT CAST(300 AS tinyint) AS rating;   -- tinyint holds 0 to 255

Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = 300.

An UPDATE pushes a smallint column past 32767

SET NOCOUNT ON;
CREATE TABLE dbo.Products (id INT PRIMARY KEY, stock SMALLINT);
INSERT INTO dbo.Products VALUES (1, 32000);

UPDATE dbo.Products SET stock = stock + 1000 WHERE id = 1;   -- 33000

Msg 220, Level 16, State 1, Line 5
Arithmetic overflow error for data type smallint, value = 33000.
The statement has been terminated.

A negative value in a tinyint column

SET NOCOUNT ON;
CREATE TABLE dbo.Adjustments (id INT PRIMARY KEY, units TINYINT);

INSERT INTO dbo.Adjustments VALUES (1, -5);   -- tinyint cannot be negative

Msg 220, Level 16, State 2, Line 4
Arithmetic overflow error for data type tinyint, value = -5.
The statement has been terminated.

How to prevent it

Choose int as the default integer type and use tinyint or smallint only when the range is fixed by definition (days of the week, a 1 to 5 rating) and protected by a CHECK constraint. Saving two or three bytes per row rarely justifies the risk of a column outgrowing its type.

Keep the types of variables, parameters and columns consistent, and validate imported numbers with TRY_CAST before loading. For a refresher on integer ranges, see SQL data types explained.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• CAST()
• CONVERT()

Official documentation

• int, bigint, smallint, and tinyint (Transact-SQL)
• TRY_CAST (Transact-SQL)
• Database Engine events and errors 0 to 999

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.