SQL Server Cursor – When (Not) to use them?

I’ll try to give an objective answer to the question – “When you should use SQL Server cursors and when not”? Since things change during the time and improvements shall be made, either on cursors, either on other objects that “replace” them, take into consideration the date when this article was written. So, let’ start.

You shouldn’t use cursors:

Almost always This might sound stupid, but that’s true in most cases. SQL Server implements a large number of objects & functions that do exactly what you would probably try to solve using cursors. Before deciding to go with the cursor, be sure you’ve investigated enough to conclude that the cursor is the only possible (good) solution. Same stands for loops in databases. In the previous article, Intro to SQL Server loops, we’ve used loops, but not to loop through data.

You could use cursors:

Mostly for database administration tasks like backups, integrity checks, rebuilding indexes
For one-time tasks when you’re sure that possible poor performance won’t impact the overall system performance
Calling a stored procedure a few times using different parameters. In that case, you would get parameters from cursor variables and make calls inside the loop

Calling a stored procedure or another query inside the cursor (or loop) impacts performance a lot, because, in each step of the cursor loop, you’ll run the query/procedure from the start. If you decide to do that, you should be aware of possible consequences.
The previous hint brings us to the last bullet when you should use cursors. If you’re completely aware of how they work and you’re pretty sure it won’t impact performance, go for it

SQL Server Cursor – Why people (don’t) use them?

The last question I would like to answer is: Why would anyone use a cursor? This is how I see it:

People who’re using them for one-time jobs or regular actions where they won’t impact performance have the excuse. One of the reasons is that such code is procedural code, and if you’re used to it, it’s very readable
On the other hand, those who started learning about databases, and are used to procedural programming might use cursors because, as mentioned, they are much closer to procedural programming than to databases. This is not a reason to use them, because the only excuse here would be that you simply don’t know the other (right) way how to get things done
The most important thing about cursors is that they are slow when compared to SQL statements, and therefore you should avoid using them because they will sooner or later lead to performance issues (unless you know exactly what you’re doing and why)

I find it useful that you understand the concept of cursors because there is a great chance, you’ll meet them along the way. They were popular before some new options were added to SQL Server. Also, there is a chance you’ll continue working on a system where somebody before you used them, and you’ll have to continue where they stopped. Maybe you’ll need to replace the cursor (procedural code) with SQL (declarative code).

Conclusion

There is no better conclusion on cursors, than – don’t use them SQL Server implemented a lot of changes that solve problems that were hard to solve using declarative code before. Better spend some time investigating and learning something new, and finally, producing optimal code. Of course, you can use them if you know why you are doing that, and you’re aware of possible problems related to them.