Cursor limitations
-
Is there a document somewhere (yes i've looked) that lists the restrictions/limitations for different cursor types. I'm mainly looking for something in an ODBC context but would be happy enough with SQLSrv specific. I have seen: http://blogs.msdn.com/mssqlisv/archive/2006/06/23/644493.aspx "Generally, a (FAST) FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC which is generally the least performant. For example, if a FAST FORWARD ODBC API Server Cursor referenced a text column, it would typically degrade to a DYNAMIC cursor, however if the query also generates an internal work table because an ORDER BY condition is not covered by an index, then the cursor would degrade to a KEYSET cursor. If at this point the query also referenced a table without a unique index the cursor degraded further to a STATIC cursor. This is an extreme example; however it illustrates the sequence of events that previous versions of SQL Server used to degrade a cursor to a progressively 'more expensive' cursor type." This is great, but i expect incomplete. Nor does it explain why a text column would cause a forward-only cursor to 'degrade', or ...
...cmk Save the whales - collect the whole set
-
Is there a document somewhere (yes i've looked) that lists the restrictions/limitations for different cursor types. I'm mainly looking for something in an ODBC context but would be happy enough with SQLSrv specific. I have seen: http://blogs.msdn.com/mssqlisv/archive/2006/06/23/644493.aspx "Generally, a (FAST) FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC which is generally the least performant. For example, if a FAST FORWARD ODBC API Server Cursor referenced a text column, it would typically degrade to a DYNAMIC cursor, however if the query also generates an internal work table because an ORDER BY condition is not covered by an index, then the cursor would degrade to a KEYSET cursor. If at this point the query also referenced a table without a unique index the cursor degraded further to a STATIC cursor. This is an extreme example; however it illustrates the sequence of events that previous versions of SQL Server used to degrade a cursor to a progressively 'more expensive' cursor type." This is great, but i expect incomplete. Nor does it explain why a text column would cause a forward-only cursor to 'degrade', or ...
...cmk Save the whales - collect the whole set
I think the only decent answer you'll get will be from the Microsoft SQL Server team. I'm assuming that you don't already have a blogs.msdn.com profile. On the top-left of the page click Sign In, then follow the screens to create a profile and log in. Once you've done this you'll be able to leave a comment on that blog entry. I suspect it's something like that: FAST FORWARD ONLY cursors only work if all the data is in-row - text, ntext and image columns are stored externally, on a different database page, to the normal data (the row contains a pointer to the text/ntext/image data). A DYNAMIC cursor only works if the data is in the order it comes from the database tables but can cope with off-row data. A KEYSET cursor requires that there's a primary key value available for the data (the cursor data consists of the set of keys). A STATIC cursor is just a complete copy of the results retrieved.
Stability. What an interesting concept. -- Chris Maunder
-
I think the only decent answer you'll get will be from the Microsoft SQL Server team. I'm assuming that you don't already have a blogs.msdn.com profile. On the top-left of the page click Sign In, then follow the screens to create a profile and log in. Once you've done this you'll be able to leave a comment on that blog entry. I suspect it's something like that: FAST FORWARD ONLY cursors only work if all the data is in-row - text, ntext and image columns are stored externally, on a different database page, to the normal data (the row contains a pointer to the text/ntext/image data). A DYNAMIC cursor only works if the data is in the order it comes from the database tables but can cope with off-row data. A KEYSET cursor requires that there's a primary key value available for the data (the cursor data consists of the set of keys). A STATIC cursor is just a complete copy of the results retrieved.
Stability. What an interesting concept. -- Chris Maunder
Thanks Mike, your remarks are in line with what i was thinking. I'll take a look a the blog page sign-in. The only reason this is an issue as some C++/ODBC code broke (a couple queries) moving from SQL Srv 2000 to Sql Srv 2005. It turns out some cursors that were being silently degraded aren't anymore. As such i want to get a better handle on what they sould be to start with. Again, thanks cmk [EDIT] FYI, Just fuond: http://msdn2.microsoft.com/en-us/library/ms190641.aspx[^] [/EDIT] -- modified at 18:53 Monday 24th July, 2006