I have always been told that Cursors create a lot of overhead and consume a lot of system resources. Is it faster to store the data in a temp table and loop through it by using Select Top 1 and Delete statements or by using a static, Forward-Only Cursor? Both ways store the data in TempDB, but doesn't the While Loop statement generate more IO's than the Cursor? In theory, I am thinking that the Cursor is better. Any info will be appreciated.
The short answer boils down to both options are bad, and you are correct that the WHILE/SELECT/DELETE choice is even worse than the cursor.
More than 99 percent of the time there is a set-based solution that is hundreds or thousands of times faster/better than any loop can be, so the set based solution is what should be your "holy grail". Fix the problem instead of trying to find ways to make the loop less of an annoyance!