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