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
|
|
