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!