SQL Server: 'String or binary data would be truncated in table' (error 2628)

String or binary data would be truncated in table 'fix6_lab.dbo.Customers', column 'Phone'. Truncated value: '+00-555-01'. (error 2628) means a value is longer than the column it is going into. The message names the table and the column, and shows the part of the value that would have fit; widen the column or shorten the value.

When the insert copies many rows, find the long ones with LEN() on the source, then decide: ALTER TABLE ... ALTER COLUMN to a size that fits real data, or LEFT() if losing the tail is acceptable.

The error

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'INSERT INTO dbo.Customers (Name, Phone) SELECT Name, Phone FROM dbo.CustomerImport'
ex.Number = 2628
Microsoft.Data.SqlClient.SqlException: String or binary data would be truncated in table 'fix6_lab.dbo.Customers', column 'Phone'. Truncated value: '+00-555-01'.
The statement has been terminated.

Why it happens

Phone was declared VARCHAR(10) and one imported row carried a 20-character phone number with an extension. SQL Server does not cut the value silently; it stops the statement. "The statement has been terminated" is literal: the two good rows of the same INSERT ... SELECT were not inserted either. The truncated value in the message is the first ten characters, which is often enough to recognise the row. In SQL Server Management Studio or sqlcmd the same failure is printed as Msg 2628, Level 16, State 1.

The detailed wording arrived in SQL Server 2019. Older servers, and databases at compatibility level 140 or lower, raise error 8152 instead, which names nothing. Turning the database setting VERBOSE_TRUNCATION_WARNINGS off on the same LocalDB brought the old text back:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = OFF'
OK (-1 row(s) affected) in 0.0 s

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'INSERT INTO dbo.Customers (Name, Phone) SELECT Name, Phone FROM dbo.CustomerImport'
ex.Number = 8152
Microsoft.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

The setting was switched back on afterwards. Its rules, including trace flag 460 for older compatibility levels, are in the ALTER DATABASE SCOPED CONFIGURATION reference.

The fix

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'SELECT Name, Phone, LEN(Phone) AS Length FROM dbo.CustomerImport WHERE LEN(Phone) > 10'
Kwame Mensah | +00-555-0100 ext 123 | 20
OK (-1 row(s) affected) in 0.0 s

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'ALTER TABLE dbo.Customers ALTER COLUMN Phone VARCHAR(20) NULL; INSERT INTO dbo.Customers (Name, Phone) SELECT Name, Phone FROM dbo.CustomerImport; SELECT Id, Name, Phone FROM dbo.Customers ORDER BY Id'
1 | Maria Garcia | 5550100
2 | David Chen | 5550101
12 | Aiko Tanaka | 5550102
13 | Kwame Mensah | +00-555-0100 ext 123
14 | Sofia Rossi | 5550104
OK (3 row(s) affected) in 0.0 s

The first query found the one row longer than ten characters; widening the column let all three rows in. Restate NULL or NOT NULL in ALTER COLUMN: in a side test, a NOT NULL column altered without it came back nullable. The new rows got Ids 12 to 14 because the failed inserts, and a deleted test of the next option, had already used the earlier identity values. If the extra characters do not matter, SELECT Name, LEFT(Phone, 10) also succeeds, and stores +00-555-01.

In EF Core the same failure arrives as a DbUpdateException ("An error occurred while saving the entity changes. See the inner exception for details.") with the 2628 SqlException inside. The fix is the same: change the length in the model and the database together, or check lengths before SaveChanges.

How it was reproduced

A console app from dotnet new console -n FixLab -f net10.0 (.NET SDK 10.0.402) with Microsoft.Data.SqlClient 7.1.1, which runs the SQL in its second argument. The test database fix6_lab on SQL Server 2022 LocalDB 16.0.1210.3 (compatibility level 160) held dbo.Customers with a VARCHAR(10) phone and a staging table with three fictional rows. The EF Core check used Microsoft.EntityFrameworkCore.SqlServer 10.0.13.

Frequently asked

How do I find which row causes String or binary data would be truncated?

On SQL Server 2019 and later the message names the table, the column and the start of the value. To list every offending row, query the source with WHERE LEN(column) > the target length.

What is the difference between SQL Server error 8152 and 2628?

Both mean a value is too long for its column. Error 2628, from SQL Server 2019 on, names the table, column and value; 8152 is the older message without those details, still raised at compatibility level 140 or lower (unless trace flag 460 is on) or when VERBOSE_TRUNCATION_WARNINGS is off.

Does ALTER COLUMN keep NOT NULL when I widen a varchar column?

Not unless you say so. Write ALTER TABLE t ALTER COLUMN c VARCHAR(20) NOT NULL; without NOT NULL the altered column becomes nullable.

Choosing string lengths before the data arrives is covered in the column types part of Database Design for Developers. More decoded errors in the Fixes category.