Daily micro-tips for C#, SQL, performance, and scalable backend engineering.
Correlated subqueries run once per row. CROSS APPLY runs more efficiently and is easier to read. Slow Subquery: SELECT c.CustomerName, (SELECT TOP 1 OrderDate FROM Orders o WHERE o.CustomerId = c.Id ORDER BY OrderDate DESC) AS LastOrderDate FROM Customers c; — Runs subquery for EVERY customer Fast CROSS APPLY: SELECT c.CustomerName, o.OrderDate AS LastOrderDate FROM […]
Writing complex analytical queries with self-joins and subqueries? Window functions perform calculations across rows without grouping. Common Window Function Patterns: — Basic structure SELECT column1, column2, WINDOW_FUNCTION() OVER ( PARTITION BY partition_column ORDER BY sort_column ROWS/RANGE BETWEEN frame_start AND frame_end ) AS calculated_column FROM table_name; — Available functions: — Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() — […]
Need running totals, rankings, or moving averages? Window functions do it in one query without complex self-joins or cursors. The Old Painful Way – Self-Join: — Calculate running total of sales SELECT o1.OrderDate, o1.Amount, (SELECT SUM(o2.Amount) FROM Orders o2 WHERE o2.OrderDate