RBAR vs. Set based programming for SQL

Question

Having read this link on RBAR and this, my understanding of RBAR amounts to this:

1. Anything that have loops and cursors
2. Anything that's not set based

I know this sounds kinda wholly which is why I am asking if anyone has a more elegant explanation as to what set-based programming is (within SQL context).

Answer 1

Set-based programming is based upon the mathematical concept of a set, and has operators that work on a whole set at a time. Procedural (RBAR) programming is based more on the traditional computer concepts of files and records. So to increase the salary of all employees in department X by 10%:

Set-based:

UPDATE employees SET salary = salary * 1.10 WHERE department = 'X';

Procedural (extreme example, pseudo-code):

OPEN cursor FOR SELECT * FROM employees;
LOOP
   FETCH cursor INTO record;
   EXIT WHEN (no more records to fetch);
   IF record.department = 'X' THEN
      UPDATE employees
      SET    salary = salary * 1.10
      WHERE  employee_id = record.employee_id;
   END IF
END LOOP
CLOSE cursor;

In the procedural version, only one employee row is being updated at a time; in the set-based version, all rows in the "set of employees in department X" are updated at once (as far as we are concerned).

Not sure this adds anything to what you will have already read in your links, but I thought I'd have a shot at it!

Answer 2

I will point out that set-based processing can involve loops. If you want to process in batches rather than tie up several tables while you load a million records into them in one set-based process, you can loop through batches of records, this is faster than a cursor which operates one row at a time and may be far better for your database than doing one giant insert statement.

Some RBAR processes don't look like cursors or loops either. These would include correlated subqueries and many user-defined functions.