DYNAMIC CURSOR & FORWARD_ONLY CURSOR - SQL Server

DYNAMIC CURSOR

In dynamic cursor you can see any kind of changes happening i.e. either inserting new records or changes in the existing and even deletes in the BASE TABLE - How ?

Means i want to know : Does it points to BASE TABLE directly in the same database instead of tempdb or while fetching the data it will re-evaluate the cursor ? etc... Just want to know internally what is happening !

FORWARD_ONLY CURSOR

In forward_only cursor also you can see any kind of changes happening i.e. either inserting new records or changes in the existing and even deletes until you have specified "STATIC" or "KEYSET" - How ?  Just want to know internally what is happening !

Please do NOT provide MSDN !!

Thanks
milani_lucieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
Static cursor is pointing to tempdb, Dynamic is to base table.

Forward_only follows the static/dynamic type, only dynamic is the default so that's why it can show changes for rows that are changed before they are fechted.

Additional, keep in mind that using cursors can generate performance and/or locking problems. If you can do something without cursor don't hesitate.  
Some 'purists' claim that everything can be done without cursor. I tend to say that before using a cursor you must be sure it realy is the only or best way to do it, ...... best after consulting a T/SQL expert that is.
Scott PletcherSenior DBACommented:
>> Does it points to BASE TABLE directly in the same database instead of tempdb or while fetching the data it will re-evaluate the cursor ? <<

Yes, a dynamic cursor points directly to the underlying table(s).  Static (and keyset) cursors store a copy of the relevant data in tempdb.

Forward_only has some performance improvement since SQL "knows" that it never has to go back to a previous row.  But the cursor still points to the live table(s) (unless it is also static, of course).

To get the next row from a cursor, SQL follows its pointers in the data.  Say you're reading names from 'K' to 'M'.

The first 'K' name is 'Kay', so SQL reads it.  That points to 'Ken'; SQL reads it.  While you're processing that row, someone updates 'Kay' to be 'Kenny'.  Then you FETCH again.  SQL's pointers now show 'Kenny' as the next row, so SQL reads it **even though it already processed that row as 'Key'**.  SQL doesn't "know" that or try to keep track of it.

Likewise DELETEs can remove rows *after* you've read them.  And INSERTs can add rows, then be rolled back after you've read it, meaning you've read a row that never really existed ("ghost read").

Again, the cursor makes no attempt to keep track of all this.

You have to decide how much of a risk such things really are and if, therefore, you want to use a static vs a dynamic cursor.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
CORRECTION:
... even though it already processed that row as 'KAy' ... --should be 'Kay', NOT 'Key'
dqmqCommented:
With a Dynamic cursor you can see changes because fetches go directly against the base table, which can change between the open and the fetch.

Static cursors become a snapshot in tempdb, so you cannot see changes

Keyset cursors make a tempdb snapshot of the primary key, which is then used to fetch the row from the base table.  Thus, the keyset itself is static, but the rest is dynamic.

Forward_only can be any of the above, but it defaults to dynamic.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.