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.
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
Part 1: How cursors work
Part 2: Cursor advantages
Part 3: Cursor disadvantages
Part 4: Cursor example
This was first published in March 2006