Search code examples
sqlset-based

RBAR vs. Set based programming for SQL

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).

Solution

  • 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!