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

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

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.:

SELECT something
FROM tables

1 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:

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.