SQL Server 2005 – When it is acceptable to use cursors

I had someone recently ask me when it is acceptable to use cursors, because, believe it or not, there are acceptable circumstances where they should be considered.

Firstly, if you’re performing a one off batch process where the execution time doesn’t really matter, I mean, why not?  Whatever gets the job done, done quickly, and done in a form that is clear enough for you to understand – although that said, some would call it bad form because they consider that it doesn’t give you practice with “correct” set-based SQL.

Secondly, take a look at MVP Greg Low’s post on Avoiding Blocking Issues in ASP.Net Session State Databases. Here, Greg describes a scenario where the use of set-based SQL actually causes slower querying due to blocking that occurs while rows that are no longer being used are cleaned up.

More importantly, this has an implication in any database where you are using temporary session-style records that need to periodically be cleaned up. One scenario that I came across recently was with the building of advanced queries – the queries have multiple query parts and it was decided to store the state in the database against the session and user, but to not specifically use session objects to store this information. After a period of time, however, unused queries are cleaned up. This could potentially create the same sort of blocking problem that Greg is talking about in his article above.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: