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.

    Requires Free Membership to View

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

This was first published in March 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.