Daily micro-tips for C#, SQL, performance, and scalable backend engineering.
Nested subqueries are impossible to read. CTEs (Common Table Expressions) break them into named, readable chunks. Messy Nested Query: SELECT * FROM Orders o WHERE o.CustomerId IN ( SELECT CustomerId FROM Customers WHERE Country IN ( SELECT Country FROM TopCountries WHERE Revenue > 1000000 ) ); Clean CTE Version: WITH TopCountries AS ( SELECT Country […]
Don’t use CASE WHEN for simple NULL checks. COALESCE is cleaner. Verbose Way: SELECT CASE WHEN Name IS NULL THEN ‘Unknown’ ELSE Name END AS Name FROM Users; Clean Way: SELECT COALESCE(Name, ‘Unknown’) AS Name FROM Users; Multiple Fallbacks: SELECT COALESCE(MobilePhone, WorkPhone, HomePhone, ‘No phone’) AS Phone FROM Users; Returns first non-NULL value.
Inserting if row doesn’t exist, updating if it does? MERGE statement does both in one atomic operation without race conditions. The Problem – Separate Insert/Update: — Check if exists IF EXISTS (SELECT 1 FROM Products WHERE ProductId = @Id) BEGIN — Update UPDATE Products SET ProductName = @Name, Price = @Price WHERE ProductId = @Id; […]
Common Table Expressions (CTEs) make queries readable but can cause performance disasters when SQL Server evaluates them multiple times. The CTE Performance Trap: — ❌ BAD: CTE evaluated 3 times! WITH RecentOrders AS ( SELECT CustomerId, OrderDate, Total FROM Orders WHERE OrderDate >= DATEADD(month, -1, GETDATE()) — Complex calculation that takes 5 seconds ) SELECT […]
Created an index but your query still takes 30 seconds? SQL Server might be ignoring it due to implicit conversions, functions, or wildcard positioning. Index Killer #1 – Functions on Indexed Columns: — Index exists on CreatedDate CREATE INDEX IX_Orders_CreatedDate ON Orders(CreatedDate); — ❌ BAD: Index ignored, full table scan SELECT * FROM Orders WHERE […]