Cursor Types

ODBC defines four cursor types supported by Microsoft® SQL Server™ and the SQL Server ODBC driver. These cursors vary in their ability to detect changes to the result set and in the resources, such as memory and space in tempdb, they consume. A cursor can detect changes to rows only when it attempts to refetch those rows; there is no way for the data source to notify the cursor of changes to the currently fetched rows. A cursor’s ability to detect changes not made through the cursor is also influenced by the transaction isolation level.

These are the four ODBC cursor types supported by SQL Server:

• Forward-only cursors do not support scrolling; they only support fetching rows serially from the start to the end of the cursor.
• Static cursors are built in tempdb when the cursor is opened. They always display the result set as it was when the cursor was opened. They never reflect changes to the data. SQL Server static cursors are always read-only. Because a static server cursor is built as a work table in tempdb, the size of the cursor result set cannot exceed the maximum row size allowed by SQL Server.
• Keyset-driven cursors have the membership and order of rows in the result set fixed when the cursor is opened. Changes to nonkey columns are visible through the cursor.
• Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set. The data values, order, and membership of the rows in the result set can change on each fetch.

See Also

Cursor Types