Every SQL Server developer hits this one early, and it’s usually the first real evidence that a column’s declared size doesn’t match what the application is actually sending it. Since SQL Server 2019 (with the database in a current compatibility level), the error is genuinely useful, it tells you the actual value that didn’t fit. Before that, and still today under ANSI_WARNINGS OFF, the behavior is very different, and the difference matters more than it looks.
The Modern Error (Msg 2628)
CREATE TABLE dbo.TruncTest (id INT IDENTITY PRIMARY KEY, code VARCHAR(5));
INSERT INTO dbo.TruncTest (code) VALUES ('TOOLONGVALUE');
Msg 2628, Level 16, State 1
String or binary data would be truncated in table 'tempdb.dbo.TruncTest',
column 'code'. Truncated value: 'TOOLO'.
The statement has been terminated.
This is the actual message SQL Server produces on a current compatibility level (150+, current instance tested at 170): it names the table, the column, and shows exactly what the value would have been truncated to. The insert fails, nothing gets written, that’s the correct, safe default behavior, a value that doesn’t fit gets rejected rather than silently cut down.
The Behavior That Actually Causes Silent Data Loss
The dangerous version of this isn’t the error, it’s the case where there’s no error at all:
SET ANSI_WARNINGS OFF;
INSERT INTO dbo.TruncTest (code) VALUES ('TOOLONGVALUE');
SET ANSI_WARNINGS ON;
id code
----------- -----
2 TOOLO
No error, no warning, the row is inserted, and 'TOOLONGVALUE' became 'TOOLO' with nothing telling anyone it happened. SET ANSI_WARNINGS OFF is the reason: some drivers, some legacy application frameworks, and some explicit SET statements in older code disable ANSI warnings for other reasons (certain indexed-view or computed-column requirements historically needed it off), and truncation silently going through is a side effect most people setting it don’t realize they’re accepting. This is the version of this problem that actually costs someone data, not the version that throws a visible error.
Diagnosing an Existing Case
When this shows up in an application that’s already live, the fix is rarely “just make the column bigger” without checking what’s actually driving the length first:
-- What's the real length distribution of data trying to go into this column?
SELECT LEN(source_column) AS length, COUNT(*) AS n
FROM staging_or_source_table
GROUP BY LEN(source_column)
ORDER BY length DESC;
-- What's the column actually declared as right now?
SELECT c.name, t.name AS type_name, c.max_length, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.TruncTest');
max_length on sys.columns is in bytes, not characters, for NVARCHAR/NCHAR divide by 2 to get the character count; for VARCHAR/CHAR it’s already the character count. A column reported as max_length = 10 for an NVARCHAR column holds 5 characters, a common source of confusion when comparing against what the application thinks the limit is.
Common Causes and Fixes
- A genuinely undersized column for legitimate data (a
VARCHAR(50)company name field that occasionally needs 60 characters). The real fix is widening the column,ALTER TABLE ... ALTER COLUMNafter confirming no code depends on the old width viasys.columnsand application-layer validation. - Bad or unexpected input the column correctly rejected. Not every truncation error is a schema problem, sometimes the incoming value genuinely shouldn’t be that long (a data quality issue upstream, a scraping or import job pulling in garbage). Fix the source, not the column, in this case.
NVARCHARvsVARCHARbyte-length confusion. A column sized assuming character count when it’s actually byte count (or vice versa) silently has half the intended capacity. Checksys.columns.max_lengthdirectly rather than trusting the number in theCREATE TABLEscript matches what’s actually enforced.- Legacy
SET ANSI_WARNINGS OFFcode paths. Search for this setting in older stored procedures, connection string options, or driver defaults, this is the actual silent-data-loss path and worth actively hunting down rather than assuming it’s not in use anywhere. - Implicit conversions from a wider type to a narrower one in an
INSERT ... SELECTor a computed column, the truncation can originate somewhere other than the literalINSERTstatement itself; check any intermediate casts or conversions in the query, not just the target column’s declared width.
Best Practices
- Never rely on
ANSI_WARNINGS OFFas a way to make a truncation error “go away”, it doesn’t fix the underlying mismatch, it just hides it and lets bad data through silently. - When widening a column to fix this, check every index, computed column, and constraint referencing it first, a width change can have knock-on effects beyond the immediate error.
- Treat this error as useful signal on a new integration, not just a nuisance, an unexpectedly long value is often the first sign the source system’s data doesn’t match what was assumed during design.
- Audit for
SET ANSI_WARNINGS OFFacross the codebase periodically; it’s easy to inherit from an old connection string or ORM default and forget it’s there.
Related Scripts
- Get Query Performance Deep-Dive, useful for finding exactly which query or procedure is generating a truncation error in an existing workload
- Collect Health and Configuration Baselines, for auditing session-level settings like
ANSI_WARNINGSacross an instance - Performance and Troubleshooting (area)
Leave a Reply