SQL Server Performance

Yes more cursor questions ??

Discussion in 'T-SQL Performance Tuning for Developers' started by seanlo@jdg.co.za, Apr 10, 2006.

  1. seanlo@jdg.co.za New Member

    Hi

    I have been able to find this default cursor behaviour in BOL.
    So if anyone can take the time to answer these thank you very much:
    These questions apply to a stored procedure that uses cursors, this sp may be fired simutanously many times.

    1.I presume cursor's are like temp table , that is a cursor declaration is unique to each connection. If 10 users fire the sp at the same time they wont get a cursor alrady exists error, i am sure they wont but just want to be certain.

    2.What happens to a cursor in the following scenario,

    BEGIN TRAN

    DECLARE CURSOR
    OPEN CURSOR

    FETCH NEXT FROM CURSOR

    @@ERROR <> 0
    ROLLBACK
    RETURN

    Is the cursor automatically closed and deallocated by the ROLLBACK or disconnection, or either ?
    Do i have to worry about closing and deallocating the cursor in every single error part of the transaction ?

    Thanx
  2. Adriaan New Member

    1. This is mentioned in BOL! If you don't specify GLOBAL or LOCAL when declaring a cursor, it depends on the default to local cursor database option. As always, be specific - make it LOCAL to avoid surprises.

    2. Always CLOSE and DEALLOCATE, especially if you're in a loop. AFAIK, you should repeat this before the RETURN instruction, since execution stops at RETURN.
  3. seanlo@jdg.co.za New Member

    Hi Adriaan

    Thanx again, yes i reckon LOCAL is safe, i do currently deallocate and close cursors in error logic just wanted to make sure i was not wasting my time.

    Thanx
  4. Madhivanan Moderator

    If possible avoid cursor as suggested in your other question

    Madhivanan

    Failing to plan is Planning to fail

Share This Page