// SQL Server error reference

SQL Server error 8152: String or binary data truncated

A value is longer than the column it's going into, so SQL Server stops the INSERT or UPDATE instead of cutting it. Newer versions name the column (2628).

Error number
8152
Severity
16
Category
Data conversion and truncation
Error message
String or binary data would be truncated.

What it means

SQL Server won't silently shorten data. If a string or binary value is longer than the target column, for example 60 characters into a varchar(50), the statement fails. Error 8152 doesn't say which column or row; that made it hard to debug, so SQL Server 2019 replaced it with error 2628, which names the table, column and the truncated value.

Common causes

  • Data longer than the column's declared length, often from user input or an import.
  • A column that is too narrow for real data, such as nvarchar(50) for e-mail addresses or names.
  • An INSERT ... SELECT whose columns are in a different order than the target's column list, so a long column lands in a short one.
  • A variable or table variable declared too short in a procedure, which truncates silently on SET but fails on INSERT.

How to fix it

  1. On SQL Server 2019 and later, get error 2628 with the column and value instead. It is on by default at database compatibility level 150 and above; you can also turn it on explicitly.

    ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;
  2. Compare the source lengths with the target columns to find the culprit.

    SELECT MAX(LEN(Email)) AS max_email, MAX(LEN(Name)) AS max_name FROM staging.Customers;
    SELECT name, max_length, TYPE_NAME(user_type_id) AS type_name
    FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Customers');
  3. Widen the column if the data is legitimate.

    ALTER TABLE dbo.Customers ALTER COLUMN Email nvarchar(320) NOT NULL;
  4. Or shorten on purpose where truncation is acceptable.

    INSERT INTO dbo.Notes (Summary) SELECT LEFT(Body, 200) FROM staging.Notes;
  5. Always name the target columns in INSERT ... SELECT so the order can't drift.

Reproduce it safely

CREATE TABLE #t (code varchar(3));
INSERT INTO #t VALUES ('ABCD');  -- 8152, or 2628 on SQL Server 2019+
DROP TABLE #t;

Questions

How do I find which column is being truncated?

On SQL Server 2019 or later at compatibility level 150, the error becomes 2628 and names the table, column and value. On older versions, compare MAX(LEN(...)) of each source column with the target column lengths.

Can I make SQL Server truncate silently instead?

SET ANSI_WARNINGS OFF makes inserts truncate without error, but it also changes other behavior and isn't allowed with indexed views or computed column indexes. Prefer an explicit LEFT() where truncation is intended.

Related errors

More data conversion and truncation errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.