What is set-based thinking in SQL?
Quick Answer
Set-based means describing what result you want as an operation over whole sets of rows, and letting the engine work out how. Row-by-row (procedural) means looping and processing one record at a time. SQL is optimised for set-based work — the optimiser can parallelise, batch I/O and pick efficient algorithms — so set-based solutions are typically far faster.
Set-based means you express the whole operation as one transformation over sets of rows and let SQL Server figure out the mechanics — it can then parallelise and batch things. Row-by-row means looping and handling one record at a time, which fights against how the engine is built. So instead of “for each order, look up the customer and update the total,” I write a single UPDATE ... FROM Orders JOIN Customers. Running totals become window functions, per-row insert-or-update becomes MERGE. The shift is to stop thinking about one row and think about the set.
- Instead of “for each order, look up the customer, calculate, update” → one
UPDATE ... FROM ... JOIN. - Instead of “loop and build a running total” →
SUM(...) OVER (ORDER BY ...). - Instead of “for each row decide insert or update” →
MERGEor an upsert pattern. - The mindset shift: stop thinking about the individual row and its neighbours; think about the transformation from one set to another.