Sql-server – Why choose a top query and temporary table instead of a cursor for a loop

cursorssql servert-sql

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

Best Answer

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:

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.

Related Question