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 a Query Plan Silently Ignoring a Foreign Key for Join Elimination Because the Constraint Was Added WITH NOCHECK and Is Marked Untrusted

- 04.11.26 - ErcanOPAK

🗄️ Fix a Query Plan Silently Ignoring a Foreign Key for Join Elimination Because the Constraint Was Added WITH NOCHECK and Is Marked Untrusted

SQL Server’s optimizer can use a trusted foreign key constraint to simplify certain queries — for example, eliminating a join to a referenced table entirely when the query doesn’t actually select any of that table’s columns, since a trusted FK guarantees every referencing row really does have a matching parent. A foreign key created or re-enabled with WITH NOCHECK skips validating the existing data at creation time, and SQL Server marks that constraint as not trusted (is_not_trusted = 1 in sys.foreign_keys) to reflect that it can’t actually guarantee referential integrity for rows that existed before the check was skipped. The optimizer treats an untrusted constraint as if it isn’t there at all for these simplifications, which means a query that could have skipped an entire join keeps performing it, with no error and nothing in the query’s results different — only the plan, and the performance, quietly worse than it should be.

🔎 The Problem

SELECT o.OrderId, o.OrderDate
FROM Orders o
JOIN Customers c ON c.CustomerId = o.CustomerId;
-- If the FK Orders.CustomerId -> Customers.CustomerId is trusted,
-- the optimizer can prove every Orders row already has a matching
-- Customers row and drop the join entirely, since no Customers
-- column is actually selected.

SELECT * FROM sys.foreign_keys WHERE name = 'FK_Orders_Customers';
-- is_not_trusted = 1  <- added WITH NOCHECK (or never revalidated);
-- the optimizer can't rely on it, so the join elimination never happens.

✅ The Fix

  • Find untrusted constraints with SELECT name FROM sys.foreign_keys WHERE is_not_trusted = 1, which lists every FK the optimizer currently can’t rely on for these kinds of simplifications.
  • Revalidate a constraint you believe is actually fine with ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers, which scans the existing data once and flips the constraint back to trusted if everything actually matches.
  • If the existing data genuinely has violations that WITH NOCHECK was used to paper over, fix those rows first — trusting a constraint that doesn’t actually hold is worse than an untrusted one, since it would let the optimizer make incorrect assumptions about data that doesn’t support them.

🚨 Why This Is Dangerous

  • An untrusted constraint affects more than just this one join-elimination case — it’s a standing signal to the optimizer that your own data might not actually satisfy a relationship your schema claims to enforce, which can quietly rule out other, unrelated optimizations across the database that also would have relied on it.

A foreign key that exists but isn’t trusted gives you the schema documentation without the optimizer benefit — it looks enforced, but SQL Server knows better than to actually rely on it.

— Database Engineer

Related posts:

How to get difference between 2 tables in MSSQL

How to get difference between 2 tables in MSSQL

SQL Server: Fix a Temporal Table's History Table Growing Forever Because Nobody Ever Configured a Re...

SQL Server: Fix a Temporal Table's History Table Growing Forever Because Nobody Ever Configured a Re...

How to list all tables referencing a table by Foreign Key in MS SQL

How to list all tables referencing a table by Foreign Key in MS SQL

Views: 11

Post navigation

The AI Prompt That Finds Every Place Your API Silently Changes Behavior Based on Header Casing Because the Framework’s Header Lookup Isn’t Case-Insensitive Everywhere
WordPress: Fix wp_remote_get Silently Failing on a URL That Works Fine in a Browser Because sslverify Defaults to True and Rejects a Self-Signed Certificate

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