Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix String Data Getting Silently Truncated Instead of Erroring Because SET ANSI_WARNINGS Was OFF for That Session

- 22.10.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

How to make pagination in MS SQL Server

How to make pagination in MS SQL Server

SQL Date Comparisons Kill Indexes

SQL Date Comparisons Kill Indexes

How To Get All Row Count For All Tables In SQL Server Database

How To Get All Row Count For All Tables In SQL Server Database

Views: 8

Post navigation

HTML5: Fix a step Attribute on a Number Input That Silently Rejects Valid-Looking Values Because of Floating-Point Rounding
The AI Prompt That Turns a Shared Household Chore List Into a Fair Rotating Schedule Everyone Actually Follows

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com