RE: What is a "set-based loop?"

SSC Guru

Points: 1003894

April 27, 2015 at 11:08 am

Eric M Russell (4/27/2015)
Jeff Moden (4/26/2015)
...
The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
Are you talking about "nested loop" join operations?

That's an obvious example but not necessarily. The pseudo-cursors are also present for merge and hash joins as well as straight forward reads from a single table/index..

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)