String or Binary Data Would Be Truncated: SQL Fix

Why this happens

CREATE TABLE customers (name VARCHAR(10));

INSERT INTO customers VALUES ('Alexandria Montgomery');
-- Msg 8152, Level 16, State 30:
-- String or binary data would be truncated.

'Alexandria Montgomery' is 21 characters; the column only allows 10. Rather than silently keeping the first 10 characters and discarding the rest, SQL Server rejects the whole statement — a deliberate safety behavior, since silent truncation is exactly the kind of thing that corrupts data without anyone noticing until much later.

The modern message: SQL Server 2019+

On SQL Server 2019 and later, with the database's compatibility level set to 150 or higher, the error message itself names the exact table, column, and value:

String or binary data would be truncated in table 'mydb.dbo.customers', column 'name'.
Truncated value: 'Alexandria M'.

Check the compatibility level if this detail isn't showing up on a 2019+ instance:

SELECT name, compatibility_level FROM sys.databases WHERE name = 'mydb';

-- Raise it if needed (test compatibility impact first on anything but a new database)
ALTER DATABASE mydb SET COMPATIBILITY_LEVEL = 150;

Older SQL Server: trace flag 460

On earlier versions, or a database still on an older compatibility level, enabling trace flag 460 for the session produces the same detailed message without changing the compatibility level itself:

DBCC TRACEON(460);
-- Re-run the failing statement — the detailed message now appears

Finding it manually without either

If neither option is available, compare the length of each candidate value against its column's defined maximum directly:

SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'customers';

-- Then, for the specific row being inserted:
SELECT LEN('Alexandria Montgomery') AS value_length;  -- 21, vs. column max of 10

On a wide table with many VARCHAR columns, this becomes tedious quickly — it's the strongest practical argument for enabling trace flag 460 rather than doing this comparison by hand every time.

Fix 1 — Widen the column

If the data is legitimate and just needs more room, this is almost always the right fix:

ALTER TABLE customers
ALTER COLUMN name VARCHAR(100);

Size it for the realistic maximum the field will ever need, not just barely enough to fix today's one failing row — a column widened from 10 to 22 characters just to fit one name will likely fail again on the next slightly longer one.

Fix 2 — Validate before insert

If the oversized value represents bad or unexpected input rather than a genuinely too-small column, validating and rejecting it with a clear message — at the application layer, before it ever reaches the database — is usually better than either widening the column indefinitely or silently truncating:

IF LEN(@customer_name) > 100
  THROW 50000, 'Customer name exceeds maximum allowed length.', 1;

Common mistakes

• Widening the column to the exact length of today's failing value instead of a realistic maximum, guaranteeing the same error resurfaces on the next slightly longer input.
• Silently truncating data with SUBSTRING() as the default fix without considering whether losing those characters actually matters for that field.
• Not checking compatibility level before assuming the detailed error message isn't available on SQL Server 2019+.
• Forgetting trace flag 460 is session-scoped (with DBCC TRACEON) — it needs to be re-enabled in a new session if it was set only for the current connection.