Guru's Guide to SQL Server Architecture and Internals, The

Types of Cursors

There are four types of cursors supported by Transact-SQL: FORWARD_ ONLY, DYNAMIC, STATIC, and KEYSET. The primary differences between these types is in the ability to detect changes to their underlying data while the cursor is being traversed and in the resources (locks, tempdb space, and so on) they use.

Depending on the type of cursor you create, changes made to its underlying data may or may not be shown while traversing the cursor. In addition to new column values, these changes can affect which rows are returned by the cursor (membership) as well as the ordering of those rows. Also, opening the cursor may cause the entirety of its result set (or their keys) to be placed in a temporary table, possibly causing resource contention ...