SQL Server: Why Your SQL Server Queries Return Wrong Results (And You Don’t Know It). Silent Killes. The "45 Seconds DBA Series" | Part 16

Before we dive into today's topic, if you missed my previous post you can take a look at Check Parameter Sniffing in 45 Seconds. Execution Engine | Part 15.

👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.

SQL Server: Why Your Queries Return Wrong Results (And You Don’t Know It)

Silent Killers | Part 16

Performance is nothing without accuracy. Stop trusting your reports blindly.

Performance is irrelevant if the data is wrong. In this post, I’ll expose the silent killers that cause SQL Server to return incorrect results without throwing a single error—a nightmare for any Senior DBA.

🧠 TL;DR BOX

✔️ NOT IN + NULL: If your subquery contains a single NULL, your entire result set becomes empty 💣
✔️ NOLOCK (Dirty Reads): Can cause duplicate rows or missing rows due to page splits during a scan 🚀
✔️ Floating Point Imprecision: Using FLOAT for financial data leads to catastrophic rounding errors 🧪
✔️ Implicit Conversions: Can change the precision of your filters, leading to "ghost" records ✔️

Hi SQL SERVER Guys and Gals,

We all know how important it is to make every second count when managing SQL Server environments. Usually, we focus on speed. But today, we focus on truth. There are specific T-SQL patterns that are technically valid but logically fatal. Let’s look at the "Silent Killers" of data integrity.

🔍 DIAGNOSIS

🧠 What it really is: Silent data errors occur when the SQL Server Engine follows the technical rules of the query, but those rules conflict with the logical intent of the business. The most common culprit is a misunderstanding of Three-Valued Logic (TRUE, FALSE, UNKNOWN).

  • ⚡ The NOT IN Trap: In SQL Server, NOT IN uses equality comparisons. If a subquery returns a NULL, the comparison becomes UNKNOWN. Because NOT UNKNOWN is still not TRUE, SQL Server discards all rows.
  • ⚡ The NOLOCK Hallucination: Using READ UNCOMMITTED (NOLOCK) doesn't just mean you see uncommitted data. It means your scan can follow a page chain that changes while you are reading it, resulting in the same row being read twice or skipped entirely.

🧪 QUERY

Run this example to see how a single NULL can destroy your reporting logic.

-- 🔍 Silent Killer: The NOT IN NULL Trap
-- ⚡ Imagine a list of users and a list of 'Excluded' users with a NULL entry

DECLARE @Users TABLE (ID INT, Name VARCHAR(50));
INSERT INTO @Users VALUES (1, 'Luca'), (2, 'John'), (3, 'Sarah');

DECLARE @Excluded TABLE (ID INT);
INSERT INTO @Excluded VALUES (2), (NULL); -- The "Silent Killer"

-- 💣 This query returns ZERO rows, even though Luca and Sarah are not excluded.
SELECT * FROM @Users 
WHERE ID NOT IN (SELECT ID FROM @Excluded);

-- ✔️ The SAFE FIX: Use NOT EXISTS
SELECT * FROM @Users u
WHERE NOT EXISTS (SELECT 1 FROM @Excluded e WHERE e.ID = u.ID);

🚀 FIX

To prevent these silent killers from poisoning your production data:

  • 🚀 Ditch NOT IN: Standardize on NOT EXISTS. It handles NULLs predictably and is often faster due to better execution plans.
  • 🚀 Kill NOLOCK: If you need non-blocking reads, use Read Committed Snapshot Isolation (RCSI). It provides consistent results without the risk of duplicate/missing rows.
  • 🚀 Use DECIMAL: Never use FLOAT or REAL for primary keys or financial calculations. Use DECIMAL(p,s) for deterministic math.

🚀 My REAL Strategy

In these years of experience, I’ve seen million-dollar errors caused by NOLOCK on high-traffic systems. Developers think it's a "go fast" button, but when a page split happens during an index scan, the pointer can move, making you count a transaction twice. My Rule: If the data must be right, NOLOCK is forbidden. Always validate your Three-Valued Logic in WHERE clauses before deployment.

📊 TAKEAWAY

A fast query that returns the wrong answer is worse than a slow query. Master Three-Valued Logic, understand how READ UNCOMMITTED physically scans pages, and never trust a NOT IN clause with nullable columns. Accuracy is the foundation of performance.

Related Posts

📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.

Biondi Luca @2026 - Sharing over 25 years of Gained Knowledge for Passion. Share if you like my posts!

Sources: Microsoft: Table Hints (NOLOCK) | Microsoft: IN (Transact-SQL)

🧠 Master SQL Server Performance Hub

Expand your knowledge with my latest deep-dives and performance guides.

Performance "45 Seconds"

Indexing & Tuning

Execution Plans

SQL 2025 & AI

Don't forget to check my April 2026 Recap for a complete monthly summary.

Comments

Post a Comment