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
