Chapter 4. Set-based iteration, the third alternative
When reading SQL Server newsgroups or blogs, you could easily get the impression that there are two ways to manipulate data: declarative (set-based) or iterative (cursor-based). And that iterative code is always bad and should be avoided like the plague.
Those impressions are both wrong.
Iterative code isn’t always bad (though, in all honesty, it usually is). And there’s more to SQL Server than declarative or iterative—there are ways to combine them, adding their strengths and avoiding their weaknesses. This article is about one such method: set-based iteration.
The technique of set-based iteration can lead to efficient solutions for problems that don’t lend themselves to declarative solutions, because those would result in an amount of work that grows exponentially with the amount of data. In those cases, the trick is to find a declarative query that solves a part of the problem (as much as feasible), and that doesn’t have the exponential performance problem—then repeat that query until all work has been done. So instead of attempting a single set-based leap, or taking millions of single-row-sized miniature steps in a cursor, set-based iteration arrives at the destination by taking a few seven-mile leaps.
