🗄️ The INSERT That Should Have Failed but Didn’t
With ANSI_WARNINGS ON (the default for most client connections, including SSMS), inserting or updating a string value that’s too long for its target column’s declared length raises a hard error – ‘String or binary data would be truncated’ – and rejects the statement outright, but with ANSI_WARNINGS OFF (still the default for some older client libraries, certain legacy connection strings, and any session that explicitly sets it), that exact same oversized value is instead silently truncated to fit the column and the statement succeeds, with no error, no warning, and no indication anywhere that part of the value was quietly cut off.
🔎 The Problem
CREATE TABLE Customers (Email VARCHAR(30));
SET ANSI_WARNINGS OFF;
INSERT INTO Customers (Email)
VALUES ('a.very.long.email.address@example-company.com'); -- 48 chars
-- Succeeds with no error. SELECT Email FROM Customers now returns
-- 'a.very.long.email.address@exa' - silently cut to exactly 30
-- characters, with nothing in the response indicating any data
-- was lost.
SET ANSI_WARNINGS ON;
INSERT INTO Customers (Email)
VALUES ('another.very.long.email@example-company.com');
-- Fails immediately: 'String or binary data would be truncated
-- in table ... column ...' - the exact same category of problem,
-- but caught instead of silently corrupting the stored value.
✅ Fix: Make Sure ANSI_WARNINGS Is Actually On Where It Matters
- Check the connection string and driver defaults for every application and integration that writes to the database – some legacy ODBC and OLE DB configurations, and certain older driver versions, set ANSI_WARNINGS OFF by default specifically for backward compatibility with older application code that expected silent truncation.
- Add an explicit SET ANSI_WARNINGS ON at the start of stored procedures and scripts where the setting’s state can’t be guaranteed from the calling context, rather than assuming every caller connects with a client that defaults it correctly – a setting this consequential is worth being explicit about rather than inherited implicitly.
- For columns that are hit by this regularly (free-text fields fed by user input, especially), validate and reject oversized input at the application layer before the INSERT ever runs, regardless of the ANSI_WARNINGS setting – this catches the problem with a clear, actionable error message instead of depending on a session-level setting to behave the way you expect.
🚨 Why This Is Dangerous
- The exact same INSERT statement behaves completely differently depending on a session-level setting that’s invisible in the statement text itself – a query that fails loudly in SSMS during manual testing (where ANSI_WARNINGS defaults to ON) can succeed and silently corrupt data in production, where the actual client library connects with a different default.
- Truncated data passes every subsequent type and length check without complaint, since it IS now valid data for that column by definition – a corrupted email address, a cut-off URL, or a sliced legal document reference all look like completely normal, successfully-stored values to every system downstream, with the only evidence being whatever the original, complete value should have been.
ANSI_WARNINGS being OFF doesn’t make an oversized value fit the column any better – it just changes SQL Server’s answer from ‘I can’t do that’ to ‘sure, here’s the part of it that fit’, and the application almost never notices which answer it actually got.
