In this article, I'll try to show the difference between a RBAR loop, a recursive CTE and a set-based loop. When the latter was applied to a real life problem, it improved performance of a query that ran for over 25 hours and wasn't able to complete, to a query that finished in two minutes. The table had over 15 million rows and was constantly growing.
Even if you're not a SS user, and I've not been for a few years, the discussion is worth the effort. All RDBMS worth your time implement a decent CTE, so, modulo syntax, it's all transferable. If the discussion sounds a bit like the [x]apply functions in R, well, it is.