String or binary data would be truncated in table, column. Truncated value

What it means

A string or binary value doesn’t fit the column it’s going into: 17 characters for a varchar(10), 5 bytes for a varbinary(4). Rather than cut it short, SQL Server stops the statement, and none of its rows are written.

You see one of two messages for the same problem:

Msg 2628, Level 16, State 1, Server 7732422b7f56, Line 1
String or binary data would be truncated in table 'inlet.seo_sqlerr_data.people', column 'name'. Truncated value: 'Margaret H'.

Msg 8152, Level 16, State 30, Server 0694f8ca0576, Line 1
String or binary data would be truncated.

2628 tells you the table, the column, and the part of the value that would fit (Margaret H is the first 10 characters of Margaret Hamilton); for a binary column the value shows as ''. 8152 tells you nothing, so you have to compare the data with the column sizes yourself.

Which one you get

It depends on the database you run the statement in, not on the client:

A database restored or attached from an older version keeps its old compatibility level, so a 2019 or newer server can still give you 8152. To check:

SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();
SELECT value FROM sys.database_scoped_configurations WHERE name = 'VERBOSE_TRUNCATION_WARNINGS';

Common causes

1. The data is longer than the column: a long name, a URL, an address, free text pasted into a field sized for something shorter.
2. Lengths count bytes, not characters. In a varchar column with a UTF-8 collation, é takes two of its n bytes; in nvarchar, an emoji takes two of its n units. café fits a Latin-1 varchar(4) and an nvarchar(4), but not a UTF-8 varchar(4).
3. A type without a length. varchar with no length in CREATE TABLE or DECLARE means varchar(1).
4. Copying between tables of different sizes: INSERT … SELECT from a wider column, a staging table that’s bigger than the target, an import tool that guessed the sizes.
5. A trigger that writes to an audit or history table with narrower columns. 2628 names that table, not the one you inserted into.

Variables, parameters, CAST and CONVERT never raise this error: they cut the value silently. CAST(x AS varchar) without a length keeps 30 characters, and a varchar(5) variable keeps 5. So a too-short parameter in a stored procedure loses data without an error. Trailing spaces beyond the column’s length are dropped without an error too.

How to fix it

Find the values that don’t fit

For a load, check the source against the column’s size before you insert:

SELECT id, name, LEN(name) AS chars, DATALENGTH(name) AS bytes
FROM staging.people
WHERE LEN(name) > 10;

sys.columns.max_length gives each column’s size in bytes (an nvarchar(5) shows 10, and -1 means max):

SELECT c.name, t.name AS type_name, c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.people');

Widen the column

If the data is right and the column too small:

ALTER TABLE dbo.people ALTER COLUMN name varchar(100) NOT NULL;

Write the whole definition: ALTER COLUMN doesn’t keep NOT NULL or a non-default collation unless you repeat them (see cannot insert NULL).

Shorten the data on purpose

If you only need the start of the value, cut it yourself so the choice is visible:

INSERT INTO dbo.people (name) SELECT LEFT(name, 10) FROM staging.people;

SET ANSI_WARNINGS OFF also makes SQL Server truncate silently, but it changes other behaviour too (division by zero can return NULL instead of failing), and with it off, writes to tables that have indexed views or indexes on computed columns fail. Don’t use it to hide this error.

Get 2628 instead of 8152

On SQL Server 2019 or later, raise the database to compatibility level 150 or higher (test your application first: the level also changes query optimiser behaviour), and make sure VERBOSE_TRUNCATION_WARNINGS is ON:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;

At level 140 or lower, a sysadmin can turn on trace flag 460 for one session while debugging: DBCC TRACEON (460);.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, in a scratch schema in a database at compatibility level 160:

CREATE TABLE seo_sqlerr_data.people (
  id int IDENTITY PRIMARY KEY,
  name varchar(10) NOT NULL,
  tag nvarchar(5) NULL,
  hash varbinary(4) NULL
);
INSERT INTO seo_sqlerr_data.people (name) VALUES ('Margaret Hamilton');
INSERT INTO seo_sqlerr_data.people (name, hash) VALUES ('Ada', 0x0102030405);
INSERT INTO seo_sqlerr_data.people (name, tag) VALUES ('Ada', N'élan vital');

Msg 2628, Level 16, State 1, Server 7732422b7f56, Line 1
String or binary data would be truncated in table 'inlet.seo_sqlerr_data.people', column 'name'. Truncated value: 'Margaret H'.
The statement has been terminated.
Msg 2628, Level 16, State 1, Server 7732422b7f56, Line 1
String or binary data would be truncated in table 'inlet.seo_sqlerr_data.people', column 'hash'. Truncated value: ''.
The statement has been terminated.
Msg 2628, Level 16, State 1, Server 7732422b7f56, Line 1
String or binary data would be truncated in table 'inlet.seo_sqlerr_data.people', column 'tag'. Truncated value: 'élan '.
The statement has been terminated.

N'café' into a varchar(4) with collation Latin1_General_100_CI_AS_SC_UTF8 failed with Truncated value: 'caf'; N'ok👍🏽' into an nvarchar(4) failed with Truncated value: 'ok👍'. A trigger copying name into a varchar(5) audit column failed with Procedure trg_people_audit, Line 4 and named the audit table. A column declared varchar with no length refused 'ab' (Truncated value: 'a'). DECLARE @v varchar = 'abc' held a, and CAST(… AS varchar) returned the first 30 characters, both without an error. 'Ada' followed by 20 spaces went into the varchar(10) column, stored as 10 bytes.

The shared test servers’ databases are all at level 150 or higher, so 8152 was reproduced on a temporary SQL Server 2022 container (same build) where the database could be changed. At compatibility level 140:

Msg 8152, Level 16, State 30, Server 0694f8ca0576, Line 1
String or binary data would be truncated.
The statement has been terminated.

At level 160 the same insert gave 2628; at 160 with VERBOSE_TRUNCATION_WARNINGS = OFF, 8152 again; back at 140 with DBCC TRACEON (460) in the session, 2628. With SET ANSI_WARNINGS OFF, the insert succeeded and stored Margaret H. SQL Server 2019 and 2025 gave the same 2628 message.

In Inlet

When SQL Server refuses, Inlet shows its message with SQL Server error 2628, state 1, severity 16 and links to this page. To widen the column, the structure designer writes the T-SQL and keeps the column’s collation. Grid edits are staged until you commit (⌘S), and Review shows what will run first.