Explain the concept of set-based operations vs row-by-row operations.

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.