Answer
SQL is fundamentally a set-based language: it is designed to operate on collections of rows at once, not one row at a time.
Set-based operations:
- Leverage the optimizer to choose efficient algorithms (hash joins, merges, batch operations).
- Use fewer logical and physical reads for bulk operations.
- Scale more gracefully as data volume grows.
Row-by-row (RBAR) operations:
- Implement logic via cursors or loops, processing one row at a time.
- Usually lead to excessive context switching, locking overhead, and long runtimes.
- Only justified for very complex, inherently procedural business rules.
Senior-level SQL design focuses on transforming requirements into set-based patterns whenever possible, often with window functions, joins, and properly designed queries.