People often trash cursors even when they’re used for perfectly fine reasons.
I understand that this reaction may be because they’ve seen cursors misused in the past. Sometimes because they heard someone popular say it.
In either case, everything has a time and place, and there are many times when cursors aren’t the performance sucks people chalk them up to be.
Places where cursors don’t freak me out:
- Maintenance scripts (backup, checkdb, etc.)
- Building dynamic strings
- Batching modifications
- Passing per-thing parameters to a stored procedure
It might shock you to find cursors in well-respected pieces of code, like sp_WhoIsActive. But if you crack open the proc and search for “cursor”, you’ll find six of them that do different things. Do you still hate cursors?
What if I showed you Paul White his-very-self suggesting people use them?
- Rejoining range seek on nullable composite index?
- Most cost efficient way to page through a poorly ordered table?
- Calculating the Median with a Dynamic Cursor
So What Then?
Should you start out most code by writing a cursor? Absolutely not.
Should you convert every cursor to a while loop? Ehhhhh.
Should you understand when you should or shouldn’t use a cursor? Absolutely.
Some people have had pretty good careers talking about knee-jerk reactions, and I think seeing a cursor declared illicits many knee jerk reactions.
Read the code. Understand the requirements.
I tune queries all day long. The number of times someone has said THIS CURSOR IS A REAL BIG PROBLEM and been right is pretty small.
Often, there was a tweak to the cursor options, or a tweak to the query the cursor was calling (or the indexes available to it) that made things run in a more immediate fashion. I want to tune queries, not wrestle with logic that no one understands. Old code is full of that.
The number of times I’ve seen someone tell me they made something faster with totally broken logic and incorrect results is pretty high.
Thanks for reading!