SQL Server Error 2628

Why it happens

Error 2628 means a value you are writing does not fit the column it is going into. The column has a fixed maximum length, such as VARCHAR(20), and the value is longer, so SQL Server refuses to silently chop it off and stops the statement instead. This is the same underlying cause as the older error 8152.

What makes 2628 so much easier to work with is the detail in the message. Where 8152 only told you that some data would be truncated, 2628 names the table (Shop.dbo.Customers), the exact column (name), and even the offending value (Alexandra Constantinople). You no longer have to guess which column or which row is at fault, you can fix it immediately.

This improved message appears only on newer configurations. You get 2628 when the database is at compatibility level 150 or higher (SQL Server 2019 and later), or on SQL Server 2017 when trace flag 460 is enabled. On older versions or lower compatibility levels the same write raises the vaguer 8152 instead. The error is severity 16, a normal catchable data error, not a server fault.

Examples

Inserting a value that is too long for a named column

CREATE TABLE dbo.Customers (
    id   INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 'Alexandra Constantinople' is 24 characters, the column holds 20
INSERT INTO dbo.Customers (id, name)
VALUES (1, 'Alexandra Constantinople');

Msg 2628, Level 16, State 1, Line 7
String or binary data would be truncated in table 'Shop.dbo.Customers', column 'name'. Truncated value: 'Alexandra Constantinople'.

An UPDATE that overflows the column

-- The name column is VARCHAR(20); the new value is longer.
UPDATE dbo.Customers
SET name = 'Alexandra Constantinople'
WHERE id = 1;

Msg 2628, Level 16, State 1, Line 2
String or binary data would be truncated in table 'Shop.dbo.Customers', column 'name'. Truncated value: 'Alexandra Constantinople'.

The same write on an older configuration raises 8152

-- Under compatibility level 140 or lower, and without trace flag 460,
-- SQL Server gives the vaguer message with no table, column, or value.
INSERT INTO dbo.Customers (id, name)
VALUES (1, 'Alexandra Constantinople');

Msg 8152, Level 16, State 30, Line 3
String or binary data would be truncated.

How to fix it

ALTER TABLE dbo.Customers
ALTER COLUMN name VARCHAR(100);

-- The insert now succeeds
INSERT INTO dbo.Customers (id, name)
VALUES (1, 'Alexandra Constantinople');

-- Fit the value to the 20-character column
INSERT INTO dbo.Customers (id, name)
VALUES (1, LEFT('Alexandra Constantinople', 20));

-- Or check the length before inserting
IF LEN('Alexandra Constantinople') <= 20
    INSERT INTO dbo.Customers (id, name)
    VALUES (1, 'Alexandra Constantinople');

-- SQL Server 2017: enable the detailed truncation message
DBCC TRACEON(460, -1);

-- SQL Server 2019+: raise compatibility level to 150 or higher
ALTER DATABASE Shop SET COMPATIBILITY_LEVEL = 150;

How to prevent it

Size columns to the real data. Measure the longest legitimate value a column must hold and pick a width with a little headroom, rather than guessing small. For a full rundown of string and binary lengths see SQL data types explained.

Validate input length before it reaches the database. Enforce maximum lengths in the application and in form fields so over-long values are caught early, and keep your databases at compatibility level 150 or higher so any truncation that does slip through reports the clear 2628 message instead of the vaguer 8152.

Common questions