Daily micro-tips for C#, SQL, performance, and scalable backend engineering.
📝 Named Subqueries Nested subqueries everywhere? Can’t understand your own query? CTEs (WITH clause) create temporary named result sets. Makes complex queries readable. The Subquery Nightmare — ❌ Nested subqueries – Unreadable! SELECT customer_name, total_orders, avg_order_value FROM ( SELECT c.name AS customer_name, COUNT(o.id) AS total_orders, AVG(o.total) AS avg_order_value FROM customers c JOIN ( SELECT * […]
📊 SQL Superpowers Need row numbers? Running totals? Rankings? Window functions perform calculations across table rows without GROUP BY. Game-changing for analytics. ROW_NUMBER() – Assign Row Numbers — ❌ Old way: Can’t number rows easily SELECT name, salary, department FROM employees ORDER BY salary DESC; — ✅ Window function: Add row numbers SELECT ROW_NUMBER() OVER […]
🔍 See What Database Actually Does Query slow? Don’t guess. Execution plans show exactly how database executes query. Find bottlenecks, fix performance. Get Execution Plan — SQL Server SET STATISTICS IO ON; SET STATISTICS TIME ON; — Show estimated plan (doesn’t run query) SET SHOWPLAN_TEXT ON; GO SELECT * FROM orders WHERE user_id = 123; […]
📝 Name Your Subqueries Nested subqueries everywhere? Unreadable mess? CTEs (Common Table Expressions) name intermediate results. Code becomes self-documenting. The Subquery Nightmare — ❌ Nested subqueries: Hard to read SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count, (SELECT AVG(total) FROM orders o WHERE o.user_id = u.id) as avg_order FROM users […]
⚡ Materialized Views in SQL Server Complex aggregation query taking 10 seconds? Create indexed view. Query becomes instant. Data stays fresh automatically. Problem: Slow Aggregate Query — This runs every time, slow on large tables SELECT CategoryId, COUNT(*) AS ProductCount, AVG(Price) AS AvgPrice, SUM(Stock) AS TotalStock FROM Products GROUP BY CategoryId; — 10 seconds on […]
📊 Advanced Analytics Made Easy Need running totals, rankings, moving averages? Window functions do it in one query. No self-joins. Running Total Example — Calculate cumulative sales per day SELECT SaleDate, Amount, SUM(Amount) OVER (ORDER BY SaleDate) AS RunningTotal FROM Sales ORDER BY SaleDate; — Result: — SaleDate Amount RunningTotal — 2024-01-01 100 100 — […]
Building pagination? OFFSET FETCH is standard SQL way. — Page 1 (rows 1-10) SELECT * FROM Products ORDER BY ProductId OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; — Page 2 (rows 11-20) SELECT * FROM Products ORDER BY ProductId OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; — Dynamic pagination DECLARE @PageNumber INT = […]
NULL values breaking calculations? Use COALESCE for fallback. — Returns first non-NULL value SELECT COALESCE(Phone, Email, ‘No contact’) AS Contact FROM Users; — Default values in calculations SELECT Name, Price * COALESCE(Quantity, 0) AS Total FROM Orders; — Multiple fallbacks SELECT COALESCE(PreferredName, FirstName, ‘Unknown’) AS DisplayName FROM Users; vs ISNULL: COALESCE is ANSI standard (works […]
⚡ Faster Correlated Queries Correlated subqueries in SELECT run for every row. CROSS APPLY runs once. — ❌ SLOW (subquery runs per row) SELECT c.Name, (SELECT TOP 1 OrderDate FROM Orders WHERE CustomerId = c.CustomerId ORDER BY OrderDate DESC) AS LastOrder FROM Customers c; — ✅ FAST (CROSS APPLY optimized) SELECT c.Name, o.OrderDate FROM Customers […]
📖 Readable SQL Nested subqueries hurt readability. CTEs (Common Table Expressions) make complex queries clear. — Clear, readable, step-by-step WITH ActiveUsers AS ( SELECT * FROM Users WHERE IsActive = 1 ), RecentOrders AS ( SELECT * FROM Orders WHERE CreatedAt > DATEADD(month, -1, GETDATE()) ) SELECT u.Name, COUNT(o.OrderId) AS OrderCount FROM ActiveUsers u LEFT […]
Don’t let developers see customer credit card numbers. Mask them at the database level. ALTER TABLE Users ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ’email()’); The data remains intact in the DB, but users without special permissions only see uXXX@XXXX.com. Essential for GDPR compliance.
Stop nesting subqueries. It makes SQL unreadable. Use WITH. WITH MonthlySales AS ( SELECT Month, SUM(Amount) as Total FROM Sales GROUP BY Month ) SELECT * FROM MonthlySales WHERE Total > 10000; It reads like a story from top to bottom, making it much easier for your team to maintain.
The SQL Optimizer uses Statistics (histograms of data distribution) to decide between a Scan or a Seek. If stats are stale, SQL makes bad decisions. The Fix: Regularly run UPDATE STATISTICS MyTable. If you just performed a massive data import, this is more important than rebuilding indexes to maintain performance.
A Deadlock occurs when Task A waits for Task B, and Task B waits for Task A. The DB kills one to save the other. Prevention Strategy Implementation Access Order Always update Table A then B, never mix. Snapshot Isolation READ_COMMITTED_SNAPSHOT ON. Use DBCC TRACEON (1222, -1) to capture detailed deadlock graphs in the error […]
Even with an index, SQL might still go to the main table (Key Lookup) to find extra columns. Use INCLUDE to make it a ‘Covering Index’. CREATE INDEX IX_Users_City ON Users(City) INCLUDE (Email, Status); By including the extra columns in the index leaf nodes, SQL Server finds everything it needs in the index itself, cutting […]
Storing JSON in a NVARCHAR(MAX) column? You can query it just like a table using JSON_VALUE and OPENJSON. SELECT Name, JSON_VALUE(LogData, ‘$.IP’) as LoginIP FROM Logs WHERE JSON_VALUE(LogData, ‘$.Status’) = ‘Failed’; This provides the flexibility of NoSQL with the strict ACID compliance of SQL Server. Best of both worlds.
Protecting data between different customers in the same table is hard. Instead of complex WHERE TenantId = @id in every query, use RLS. You define a security policy at the database level. The database itself will automatically filter the rows based on the logged-in user context, making it impossible for one customer to accidentally see […]
If you query WHERE City=’NYC’ AND Age > 30, SQL doesn’t always need a single composite index. It can use Index Intersection. It scans two separate indexes and finds the overlapping rows. However, for maximum performance, a composite index (City, Age) is usually superior because it avoids the merge step.
Never physically DELETE data in enterprise apps. Use a bit column IsDeleted. But how do you handle unique constraints? Pro Tip: Use a Filtered Unique Index. This allows you to have the same email address in a ‘Users’ table if the previous ones are marked as deleted. CREATE UNIQUE INDEX UI_ActiveEmail ON Users(Email) WHERE IsDeleted […]
Before Window Functions, calculating a running total required a complex self-join. Now, it’s a one-liner. SELECT OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate) as RunningTotal FROM Sales; Why? It’s significantly faster because the database only scans the table once (O(N) vs O(N^2)).
Even with indexes, your DB can slow down due to Fragmentation. This happens after many Inserts/Deletes. The Pro Check: Run this to rebuild all indexes on a table and reclaim performance: ALTER INDEX ALL ON MyTableName REBUILD;
Subqueries are hard to read and debug. CTEs allow you to create ‘Virtual Results’ that you can reference easily. WITH HighValueSales AS ( SELECT * FROM Sales WHERE Amount > 1000 ) SELECT p.ProductName, hvs.Amount FROM HighValueSales hvs JOIN Products p ON hvs.ProductId = p.Id;
💥 The Database Meltdown 9 AM Monday. Database CPU: 98%. Queries timing out. Users can’t log in. Boss is screaming. You have 1 hour to fix it before the CEO finds out. The 5 Performance Killers (And How to Fix Them) 💣 Killer #1: SELECT * ❌ SLOW (transfers 50 columns, 10MB) SELECT * FROM […]
IN processes entire subquery. EXISTS stops at first match (faster). Slow (IN): SELECT * FROM Customers WHERE CustomerId IN ( SELECT CustomerId FROM Orders WHERE Total > 1000 ); — Processes ALL matching orders Fast (EXISTS): SELECT * FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerId = c.CustomerId AND o.Total […]
UNION removes duplicates (expensive operation). UNION ALL keeps them (fast). UNION (Slow): SELECT Name FROM ActiveUsers UNION SELECT Name FROM InactiveUsers; — Removes duplicates: Sorts, scans, filters — Slow on large datasets! UNION ALL (Fast): SELECT Name FROM ActiveUsers UNION ALL SELECT Name FROM InactiveUsers; — Keeps duplicates: No sorting or filtering — Much faster! […]
Deadlocks occur when two processes wait for each other to release locks. Learn how to use Extended Events to capture deadlock graphs and rewrite queries to avoid ‘Victim’ status.
Common Table Expressions (CTEs) are not just for readability. They allow for Recursive Queries, essential for hierarchical data like organizational charts or folder structures.
If your database is slow despite having indexes, they might be fragmented. Check fragmentation with sys.dm_db_index_physical_stats. The Fix: ALTER INDEX ALL ON MyTable REBUILD;
Modern SQL Server can handle NoSQL-style JSON data. Stop serializing/deserializing in C#; do it in SQL! SELECT JSON_VALUE(LogData, ‘$.Browser’) as Browser FROM WebLogs WHERE JSON_VALUE(LogData, ‘$.Status’) = ‘Error’
Never DELETE from production. Use an IsDeleted bit column and a Global Query Filter to hide deleted data while keeping it recoverable.