Why choose a top query and temporary table instead of a cursor for a loop?

Question

When it comes to looping over a large set of data in T-SQL, the majority of examples I see on the Internet use a pattern like this:

declare @someVariable int
declare @remainingRows int
select someColumn from someTables into #someTempTable
select @remainingRows = count(*) from #someTempTable
while @remainingRows > 0 begin
 select top 1 @someVariable = someColumn from #someTempTable
 -- Do stuff
 delete top 1 from #someTempTable
 select @remainingRows = count(*) from #someTempTable
end

This seems to be way more common than samples of using cursors like this:

declare @someVariable int
select someColumn from someTables into #someTempTable
declare @someCursor cursor for select someColumn from #someTempTable
open @someCursor
fetch next @someVariable from @someCursor
while @@fetch_status = 0 begin
 -- Do stuff
 fetch next @someVariable from @someCursor
end
close @someCursor

I sometimes hear mentioning of performance reasons, but to me that's counter-intuitive; wouldn't a forward-only read-only cursor iterating over a set of data be way more efficient than constantly querying and updating a temporary table? I understand that if it was a cursor iterating over a table with the wrong options that the table could be locked for updates, but a cursor over a non-shared temporary table wouldn't have that problem, would it?

Bonus question; if cursors are OK, would a cursor over a regular query that uses snapshot isolation be safe so I can avoid the annoyance of manually creating a temporary table, i.e.:

DECLARE @someCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
SELECT something
FROM tables

Answer 1

No, a loop is not necessarily faster than a cursor, though some people are more comfortable with them. I went through the loop writing phase myself at one time. Also cursors come in several flavors, so choosing the right type of cursor is an important detail.

Your question is probably answered by Aaron Bertrand (from 2012), since he ran several comparative tests, at:

• https://sqlblog.org/2012/01/26/bad-habits-to-kick-thinking-a-while-loop-isnt-a-cursor

In short, he found that the proper cursor ran significantly faster than several loop constructs.

Reading through his comparisons should help you feel comfortable with cursors.

But only when they are needed.

• Aaron Bertrand: Also worth a look perhaps: sqlperformance.com/2012/09/t-sql-queries/cursor-options