| Previous | Table of Contents | Next |
The allure of cursors is in their structured roots. To anyone who is comfortable with structured programming, cursors feel warm and familiar. Resist the urge to use cursors for everyday operations that could be more easily and efficiently performed with a SQL statement.
Technical Note: Cursors and locking
Cursors that are defined as read-only, the default, will place shared locks on pages that are being read. The locks result from FETCH operations. Declaring and opening a cursor does not acquire locks. As the cursor moves through the keyset, shared locks are acquired on the page holding the current row and released on old pages.
Tip: If you are having locking problems with read-only cursors, consider using the INSENSITIVE option. This will create a temporary table in tempdb holding the columns and rows selected by the cursor, which can then be read without locking restrictions. Shared locks will be acquired on the original table for only as long as the server needs to select the data into the temporary table.
A cursor that is defined as FOR UPDATE acquires update locks on pages holding the current row. If you attempt a modification of that row with an UPDATE... WHERE CURRENT OF operation, the update lock is upgraded to an exclusive lock while the change is made. After the UPDATE operation, the lock is changed back to an update lock. This ensures that no one else changes the row while you are cursoring through the table.
If you begin a transaction before modifying rows, however, no locks are released until the transaction is completed with either a rollback or a commit. In this case, locks don’t downgrade.
Cursors cannot be used to insert new rows into a table.
If you use an ORDER BY in the cursor, the order you choose must be supported by an existing index on the table or the cursor will be read-only. Scrollable cursors, contrary to MS documentation, can be updated.
If you want ANSI-compliant cursor behavior, set CURSOR_CLOSE_ON_COMMIT on. When you issue a COMMIT transaction, it will close all open cursors.
Today you learned how to program server-side cursors on the SQL Server. Cursors require a great deal more programming effort than regular SQL statements and often provide poorer performance. Sometimes, however, cursors provide a more easily understandable solution to a difficult problem.
Cursors may be defined for read-only or for update. To update or delete the current row in a cursor keyset, use the WHERE CURRENT OF <cursor name> modifier. Cursors have slightly different rules for locking and performance than normal SQL statements.
Q Why is the cursor so maligned in the newsgroups, trades, and conventional wisdom?
A Cursors are the most often misused feature of SQL Server. When used properly and sparingly, cursors complement SQL by providing an easy way to handle problems that set processing makes very difficult. Many programmers who migrated to SQL from xBase or COBOL (myself included) found the migration much easier when they discovered cursors. As a result, newcomers to SQL used cursors for everything. Using cursors for normal SELECT and DELETE operations is a recipe for horrendous performance and unmaintainable cursor code.
Q Can I use cursors inside triggers?
A Have you been skipping ahead? Yes, you can use cursors inside triggers, but doing so is strongly discouraged. Cursors require lots of overhead to create and destroy. Triggers run every time an INSERT, UPDATE, or DELETE, runs on the table. Creating, running, and destroying a cursor for each of those operations takes far longer than most users want to wait.
Q Have you ever used a cursor in a production environment?
A In the years that I’ve spent finding answers to tough SQL problems, I’ve never used a cursor for anything but a temporary solution in a development environment. This isn’t to say cursors are always bad. It’s a lot like asking a structured programmer if they’ve ever used GOTO.
Q Can I fetch more than one row at a time?
A No. Client cursors do allow multiple rows to be fetched at a single time, but server cursors do not.
Q Can I have more than one cursor active at the same time? On the same table?
A Sure.
The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.
Playing with cursors takes a lot of time. I would rather you spent your time learning about some of the advanced techniques in the later chapters. I’ve listed a single exercise for you to try here, but feel free to skip it and go on to Day 15. Indexes are a lot more interesting than cursors, anyway.
Create a cursor on the authors table. Read the author’s last name and state. If the author lives in California, print the author’s last name and the phrase lives in CA. Otherwise, print the author’s last name and the phrase doesn’t live in CA. Print the results in last-name order.
| Previous | Table of Contents | Next |