SQL Server cursors pros and cons

SQL Server cursors can be useful despite their reputation as poor performers, resource hogs and an overused tool by junior developers. This tip explains how cursors work and their pros and cons.

Conventional wisdom states that SQL Server cursors are evil! A cursor is a memory resident set of pointers that reference data in your result set or, more accurately, the data in the base tables. Cursors have a bad reputation because they are typically the favorite hammer of every junior SQL developer in search of a nail, they do not perform well and they deplete systems resources.

We all have stories about cursors we had to rewrite as set-based operations, after which batches ran considerably faster, or how mysterious server crashes were resolved when a query was rewritten without a cursor. Then why is it that when you query syscomments on text like '%cursor%' in SQL Server 2005 you find no less than 213 distinct Microsoft shipped procedures and functions that use cursors? In SQL Server 2000, there are 245 distinct procedures or functions using cursors. Either Microsoft's SQL developers are practicing poor coding practices, or cursors really do have a place in SQL development. What do you think?

I personally think cursors do have a place.

    1. They are necessary for some dynamic operations that can't be accomplished with set-based operations.
    2. They are simple to understand, which makes them ideal for quick-and-dirty programming and a tool of choice for junior SQL developers.
    3. They outperform while loops when you need row-by-row processing.
    4. They are ideal for scrolling a portion of a large results set.
    5. By default they provide a window into your tables or results set, which maximizes concurrency for all applications. So the window they provide into your data reflects updates that occur as the cursor iterates the results set and the cursor itself holds a shared lock briefly as it fetches the next row in your results set.

In this tip, I'll explain how cursors work, when to use them and when to avoid them.

Migrating to SQL Server 2005

 Home: Introduction
 Part 1: How cursors work
 Part 2: Cursor advantages
 Part 3: Cursor disadvantages
 Part 4: Cursor example

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning