This is a quick working path, not a database theory course. Read one card when you hit a problem, copy the small example, then adapt it to your schema and inspect the execution plan.
Work through the path
Open a card when you need it. Marking a lesson complete is optional and stays only in this browser.
1Use a CTE to name a query step
A common table expression gives a complex statement a named, local step without creating a permanent table.
A CTE is useful when you want to explain a query as a sequence: first find the latest order for each customer, then join that result to customer data. It exists for the single statement that follows it.
A CTE is a readability and composition tool, not a promise that SQL Server materializes the intermediate result. Compare it with a temporary table when the data volume or plan requires it.
Try it
Change the ordering to decide what latest means when two rows have the same timestamp.
;WITH LatestOrder AS
(
SELECT o.CustomerId, o.OrderId, o.CreatedAt,
ROW_NUMBER() OVER
(PARTITION BY o.CustomerId
ORDER BY o.CreatedAt DESC, o.OrderId DESC) AS rn
FROM Sales.Orders AS o
)
SELECT CustomerId, OrderId, CreatedAt
FROM LatestOrder
WHERE rn = 1;2Keep rows while calculating across them
Window functions calculate rankings, running totals or previous values without collapsing the result into one row per group.
GROUP BY changes the grain of a result. A window function keeps the original rows and adds a value calculated over related rows.
Always make the partition and order explicit. The partition says which rows belong together; the order says how SQL Server evaluates the window.
Try it
Replace SUM with ROW_NUMBER or LAG and notice that the result still has one row per transaction.
SELECT AccountId, PostedAt, Amount,
SUM(Amount) OVER
(PARTITION BY AccountId
ORDER BY PostedAt, TransactionId
ROWS UNBOUNDED PRECEDING) AS RunningBalance
FROM Finance.Transactions;3Use EXISTS when you only need to test membership
EXISTS expresses “at least one related row exists” and avoids accidental duplicate parent rows a join can introduce.
If the question is whether a customer has an unpaid invoice, you do not need invoice columns in the result. EXISTS communicates that intent.
A JOIN is correct when you need columns from the related table or when the relationship is part of the result shape.
Try it
Write the same query with a JOIN, add two unpaid invoices for one customer and observe the duplicate.
SELECT c.CustomerId, c.DisplayName
FROM Sales.Customers AS c
WHERE EXISTS
(
SELECT 1
FROM Billing.Invoices AS i
WHERE i.CustomerId = c.CustomerId
AND i.Status = 'Unpaid'
);4Keep indexed filters searchable
A SARGable predicate lets SQL Server use an index more effectively instead of applying a function to every candidate row.
Wrapping an indexed column in a function, such as CONVERT(date, CreatedAt), may force SQL Server to evaluate the function row by row. A half-open date range keeps the column untouched.
This is a starting heuristic, not a guarantee. Data distribution, indexes, statistics and the actual plan still decide what is fast.
Try it
Compare both forms on representative data and check logical reads in the actual execution plan.
-- Often less searchable WHERE CONVERT(date, CreatedAt) = @Day; -- Keep the column untouched WHERE CreatedAt >= @Day AND CreatedAt < DATEADD(day, 1, @Day);
No lesson matches that phrase.
Related tools
Turn the concept into a working example with the browser-based tools.