How do I find which column causes "String or binary data would be truncated" in SQL Server?

DBCC TRACEON (460);                -- session; use -1 for global
-- or per query:
INSERT ... OPTION (QUERYTRACEON 460);

Azure SQL Database has the detailed message too.

Find the offending rows yourself

When you're staging data (bulk loads, INSERT … SELECT), compare lengths against the target's definitions before inserting:

SELECT c.name, c.max_length, t.name AS type_name
FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Customers');
-- max_length is in bytes: nvarchar(100) shows 200, and -1 means (max)

SELECT TOP (20) Id, LEN(Email) AS len, Email
FROM staging.Customers
WHERE LEN(Email) > 100
ORDER BY len DESC;

LEN ignores trailing spaces, while DATALENGTH gives bytes. Use DATALENGTH for varbinary and for byte-based limits.

Fix it properly

1. Decide whether the data or the column is wrong. A 300-character email is probably junk; a 300-character product name may be legitimate.

2. Widen the column if the data is valid:

   ALTER TABLE dbo.Customers ALTER COLUMN Email nvarchar(320) NOT NULL;
   

   Increasing the length of a varchar/nvarchar is usually a quick metadata change. Check indexes (key size limits) and dependent objects first. Changing varchar to nvarchar rewrites the data.

3. Validate at the edge: enforce the same limits in the application or API, so users get a proper validation message instead of a database error.

4. Truncate deliberately in ETL only when it's acceptable, and log it: LEFT(Email, 320).

Watch out for silent truncation

With SET ANSI_WARNINGS OFF (some legacy connections and linked-server setups), SQL Server truncates silently instead of raising an error. Keep ANSI_WARNINGS on, which is the default for modern drivers.

Variables and parameters truncate silently too:

DECLARE @name varchar(10) = 'This is far too long';
SELECT @name;   -- 'This is fa', no error

Stored procedure parameters declared shorter than the column (@Email varchar(50) for an nvarchar(320) column) cut data before it ever reaches the table. Keep parameter types identical to column types.

Checklist

1. 8152/2628 means a value is longer than its target column.
2. On 2019+ with compatibility level 150+, message 2628 names the table, column and value; on 2016/2017 use trace flag 460.
3. Find rows with LEN/DATALENGTH against the column definitions, then widen the column or clean the data.
4. Keep ANSI_WARNINGS on, and match variable and parameter sizes to the columns.

How do I find and fix missing indexes in SQL Server?

A query-first method: find the expensive queries, read the plan for signs the missing index DMVs never report, build the index online safely, then verify and roll back.

#execution-plans#indexing#performance