5

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
  • 1
    Your question is probably answered by Aaron Bertrand (from 2012), since he ran several comparative tests, at: sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/… – RLF Dec 4 '14 at 20:08
  • @RLF: that article is exactly what I needed to see. Please post as an answer. It seems to validate my suspicion that people simply don't use cursors because they're awkward to write the code for. – Jacob Dec 4 '14 at 20:10
  • Jacob, most people don't use cursors and go with the less efficient while loop / manual cursor because they heard and/or read that "cursors are evil; never use them" and they never bothered to test it themselves or investigate. It's no different than the false beliefs that keep many people from using SQLCLR. For the bonus question: with a STATIC cursor you don't need to create a temp table as the results are copied into one behind the scenes. This is why I prefer using STATIC as the cursor then doesn't need locks on the referenced tables. But don't use STATIC if base tables are temp tables. – Solomon Rutzky Dec 5 '14 at 5:10
9

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, privacy policy and cookie policy

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