For the rest of this discussion I will be looking at the following cursor:
DECLARE authors_cursor CURSOR FOR
SELECT authors.au_id, au_lname, au_fname, phone, address, city, state, zip, contract, title FROM authors
JOIN titleauthor ON titleauthor.au_id=authors.au_id
JOIN TITLES ON titleauthor.title_id=titles.title_id
ORDER BY authors.au_id
This is a poorly written cursor because I am returning all columns I am unlikely to need and I am not using a where clause; chances are I won't need every row. Memory structures created contain tracking information about the data in the base tables. With the exception of a STATIC cursor, changes that occur in base tables underlying your results set will reflect in the fetch statement used to iterate from one row to another.
STATIC cursors copy all rows into tempdb. The cursor's internal pointers reference this read-only copy in tempdb and do not reference the base tables. Changes made to the base tables will not be "seen" by the cursor fetches.
KEYSET cursors only copy key information into tempdb, so they will not see newly inserted rows the way DYNAMIC and READONLY cursors do. If your fetch statement tries to fetch a row that has been deleted from base tables it will return nothing; @@FETCH_STATUS will be -2.
All this tracking activity requires system overhead; STATIC requires the most overhead. This decreases resources available to your system as a whole and consequently degrades your SQL Server performance. The cursor also lowers concurrency of data in the base tables. Some DBAs and developers will slurp the data into a temporary table and build the cursor off that. They also place indexes on the temporary table to prevent the cursor from having to do a complete table scan.
By default a cursor is created as DYNAMIC, OPTIMISTIC and FORWARD_ONLY. OPTIMISTIC concurrency means if you fetch the row and then update it, and someone has updated the row between the time the cursor fetched it and the time you update it, the cursor update will fail. In other words, the cursor is "optimistic" that the row it is going to update has not been updated by another process. FORWARD_ONLY means you can only use Fetch Next statements. Fetch Prior will result in the following error:
Server: Msg 16911, Level 16, State 1, Line 1
fetch: The fetch type prior cannot be used with forward only cursors.
Many DBAs do not test for @@FETCH_STATUS -2 which leads to errors in logic.
Summary
Cursors are frequently misused SQL lint programs (like the ones presented in Linchi Shea's book, Real World SQL Server Administration with Perl). Quest Software's SQL Spotlight looks for cursors in T-SQL code, as cursors are typically not the best code solution and often degrade system performance. Make sure you know what your cursors are doing and use the correct cursor default options. Ideally, you should use a STATIC cursor off a minimal results slurped into tempdb with appropriate indexes. Finally, consider how the data is consumed or manipulated. It is often better to send your results set to the consuming application and have the iterative operation performed there.
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
|
|
