WHERE Status = 1 OR Status = 2 ❌ Effect Index seek → index scan. ✅ Fix Rewrite with IN or UNION.
Category: SQL
SQL — Floating Point Columns Corrupt Financial Data
FLOAT ≠ money. ❌ Result Rounding errors accumulate silently. ✅ Fix Use: DECIMAL(18, 2)
SQL Server — LEFT JOIN + WHERE Turns Into INNER JOIN
This is a classic silent bug: LEFT JOIN Orders o ON o.UserId = u.Id WHERE o.Status = ‘Paid’ ❌ Result Rows without orders are removed. ✅ Fix Move condition into JOIN.
SQL Server — GETDATE() Breaks Deterministic Queries
Using GETDATE() inside queries makes results non-cacheable. ❌ Problem Query plans cannot be reused efficiently ✅ Fix Pass time as a parameter instead.
SQL Server — Scalar UDFs Kill Query Parallelism
Scalar functions force serial execution. ❌ Result CPU spikes Slow reports ✅ Fix Inline logic or use inline table-valued functions.
SQL Server — TOP (1) Without ORDER BY Is Undefined
SELECT TOP 1 * FROM Orders ❌ Problem Result can change between executions. ✅ Always do ORDER BY CreatedAt DESC
SQL Server — Nonclustered Index INCLUDE Is a Superpower
CREATE INDEX IX_User_Email ON Users(Id) INCLUDE (Email, CreatedAt) Why this matters Eliminates key lookups Massive speedups for read-heavy queries
SQL Server — COUNT(*) vs COUNT(column) Is Not the Same
SELECT COUNT(*) FROM Orders Why COUNT(*) is faster Doesn’t check for NULL Uses metadata when possible Avoid COUNT(column) unless required.
Implicit Data Type Conversion Breaks Index Usage
WHERE UserId = ‘123’ Column is INT. ❌ Result Index ignored Full scan ✅ Fix Match types exactly.
Why MERGE Can Corrupt Data Under Concurrency
MERGE looks elegant… but has known race issues. ❌ Risks Duplicate inserts Missed updates Hard-to-reproduce bugs ✅ Safer Pattern Explicit UPDATE + INSERT with transaction.
Why NVARCHAR(MAX) Can Destroy Query Plans
Using NVARCHAR(MAX) everywhere is tempting. ❌ Hidden cost Prevents index usage Breaks memory grants ✅ Rule Use fixed lengths when possible: NVARCHAR(255)
DATEDIFF in WHERE Clause Disables Indexes
This looks innocent: WHERE DATEDIFF(day, CreatedAt, GETDATE()) = 0 ❌ Why it’s bad Forces full scan Index becomes useless ✅ Fix WHERE CreatedAt >= CAST(GETDATE() AS date)
NOT IN vs NOT EXISTS — One Can Return Wrong Results
WHERE Id NOT IN (SELECT UserId FROM BannedUsers) ❌ Problem If subquery returns NULL, results are wrong. ✅ Always Safer WHERE NOT EXISTS ( SELECT 1 FROM BannedUsers b WHERE b.UserId = u.Id )
SELECT * Breaks Index Usage
This kills performance: SELECT * FROM Orders 🧠 Why Forces key lookups Breaks covering indexes ✅ Fix Select only what you need: SELECT Id, Total FROM Orders
Missing WHERE on UPDATE — Disaster Prevention Trick
Human error happens. ✅ Safety Pattern BEGIN TRAN UPDATE Users SET IsActive = 0 WHERE LastLogin < ‘2022-01-01’ — sanity check SELECT @@ROWCOUNT ROLLBACK
Parameter Sniffing — The Query That Works… Until It Doesn’t
Same query, different performance. 🧠 Root cause SQL caches execution plans based on the first parameter. ✅ Fix Options OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN
SQL “DELETE Is Slow” — Use Batch Deletes
Deleting millions of rows at once is dangerous. ❌ DELETE FROM Logs WHERE CreatedAt < ‘2023-01-01’; ✅ Safe Batch Delete WHILE 1 = 1 BEGIN DELETE TOP (1000) FROM Logs WHERE CreatedAt < ‘2023-01-01’; IF @@ROWCOUNT = 0 BREAK; END Why Avoids log explosion Prevents long locks
SQL “TOP 1 WITH ORDER BY” Without Index = Full Scan
This query is slow without the right index: SELECT TOP 1 * FROM Orders ORDER BY CreatedAt DESC; ✅ Correct Index CREATE INDEX IX_Orders_CreatedAt ON Orders (CreatedAt DESC); Result Instant response No table scan
SQL SELECT * — The Hidden Future Bug
Works today, breaks tomorrow. ❌ SELECT * FROM Users; ✅ SELECT Id, Name, Email FROM Users; Why Schema changes break consumers Pulls unnecessary data Slower network and memory usage
SQL “Index Fragmentation Panic” — When NOT to Rebuild
Many rebuild indexes blindly. ❌ Bad Habit ALTER INDEX ALL ON Orders REBUILD; ✅ Smart Rule Fragmentation < 5% → do nothing 5–30% → REORGANIZE 30% → REBUILD ALTER INDEX IX_Orders_Date ON Orders REORGANIZE; Why Rebuild = blocking + log growth.
SQL Index Exists but Still Not Used? Here’s Why
SQL ignores indexes when: Data type mismatch Implicit conversion Example problem: WHERE UserId = ‘123’ — string ✅ Fix WHERE UserId = 123 — int Golden rule Indexes only work when types match exactly.
SQL COUNT(*) Is Slower Than You Think
This query scans rows: SELECT COUNT(*) FROM Orders; ✅ Faster Metadata Count (Approximate) SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(‘Orders’) AND index_id IN (0,1); Use when Dashboards Monitoring Large tables
SQL “Dirty Reads & Weird Data” — Fix With SNAPSHOT Isolation
Readers block writers, writers block readers → chaos. ✔ The Fix Enable snapshot isolation: ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON; 💡 Why Reads no longer block updates → faster API.
SQL “Scalar Functions Destroy Performance” — The Hidden Anti-Pattern
Scalar UDFs run row-by-row → slowest possible execution. ✔ Replace with Inline Table-Valued Function CREATE FUNCTION GetPrice(@id INT) RETURNS TABLE AS RETURN SELECT Price FROM Products WHERE Id = @id; Then: SELECT p.*, f.Price FROM Orders o CROSS APPLY GetPrice(o.ProductId) f; 💡 Boost CPU ↓, IO ↓, query ↑.
SQL “Parameter Sniffing Hell” — The REAL Fix Nobody Uses
When SQL caches a plan for one specific parameter, other queries become slow. Most people use OPTION RECOMPILE.BUT the REAL fix? ✔ Create an optimized local variable DECLARE @local INT = @UserId; SELECT … WHERE UserId = @local; SQL can no longer sniff the parameter → balanced plan for all users.
SQL “Zombie TempDB” — The Invisible Performance Killer
TempDB grows silently until EVERYTHING slows. ✔ Fix: Right-size files ALTER DATABASE tempdb MODIFY FILE (NAME=’tempdev’, SIZE=4GB); And create multiple TempDB files: 4–8 files = massive improvement 💡 Why? TempDB is used for: joins sorting hashing version store This tweak alone boosts 90% of sluggish systems.
SQL “Blocking Chains” — The Magic of READPAST
Ever seen queries freeze because a row is locked? Use the magical, rarely-used hint: SELECT * FROM Orders WITH (READPAST) WHERE Status = ‘Pending’; This skips locked rows instead of waiting. ✔ Perfect for: Queue tables Background workers Batch processors
SQL “High CPU for No Reason” — The Missing Statistics Update
Sometimes SQL goes to 80–90% CPU “out of nowhere”because stats are stale. ✔ Emergency Fix: UPDATE STATISTICS Orders WITH FULLSCAN; 💡 Why It Works The optimizer chooses WILDLY wrong plans if statistics aren’t fresh. ⚠ Pro Tip If your DB is huge → don’t FULLSCAN often.Use: EXEC sp_updatestats; Much lighter.
SQL “Blocking Sessions” — The Hidden Killer: Orphaned Transactions
Long-running transaction = entire DB slowdown. ✔ Quick Diagnostic SELECT * FROM sys.dm_tran_active_transactions; If you see an uncommitted transaction running for hours → that’s your culprit. ✔ The Fix Enable XACT_ABORT to auto-kill faulty transactions: SET XACT_ABORT ON; This is rarely known but prevents ghost locks forever.
SQL “Slow Pagination” — The Secret Trick: Seek Method
Most devs use: ORDER BY Id OFFSET @Skip ROWS FETCH NEXT @Take ROWS This is painfully slow after page 5000+because SQL scans all skipped rows. ✔ REAL FIX: Seek Pagination SELECT TOP (@Take) * FROM Orders WHERE Id > @LastSeenId ORDER BY Id; 💡 Benefit No full scans No offsets Linear, predictable speed