Professional SQL Server™ 2005 Programming

15.3. Types of Cursors and Extended Declaration Syntax

Cursors come in a variety of different flavors (we'll visit them all before we're done). The default cursor is forward-only (you can only move forward through the records, not backward) and read-only, but cursors can also be scrollable and updatable. They can also have a varying level of sensitivity to changes that are made to the underlying data by other processes.

The forward-only, read-only cursor is the default type of cursor in not only the native SQL Server cursor engine, but is also default from pretty much all the cursor models I've ever bumped into. It is extremely low in overhead, by comparison, to the other cursor choices, and is usually referred to as being a "firehose" cursor because of the sheer speed with which you can enumerate the data. Like a fire hose, it knows how to dump its contents in just one direction though (you can't put the water back in a fire hose now can you?). Firehose cursors simply blow away the other cursor-based options in most cases, but don't mistake this as a performance choice over set operations — even a firehose cursor is slow by comparison to most equivalent set operations.

Let's start out by taking a look at a more extended syntax for cursors, and then we'll look at all of the options individually:

DECLARE <cursor name> CURSOR [LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] [TYPE_WARNING] FOR <SELECT statement> [FOR UPDATE ...