Feature

Pitfalls of using T-SQL cursors

Conventional wisdom states that 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. This window into your data reflects updates that occur as the cursor iterates the results set. The cursor itself holds a shared lock briefly as it fetches the next row in your results set.

In this feature, 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

ABOUT THE AUTHOR:   
Hilary Cotter
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
Copyright 2006 TechTarget

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: