none
Side Effects Switching Cursor Default from Global to Local

    Question

  • Hi,

    I just ran into a situation that resulted in me learning that CURSORs could be LOCAL or GLOBAL.  After reviewing the differences, we (my DBA team) decided that having the CURSOR_DEFAULT set to LOCAL is consistent with how CURSORs are used in our databases.

    So, before we haul off and change the CURSOR_DEFAULT setting of all our databases, does anyone have any battle tales regarding changing CURSOR_DEFAULT from GLOBAL to LOCAL?


    Dan Jameson
    Manager SQL Server DBA
    CureSearch for Children's Cancer
    http://www.CureSearch.org

    Monday, March 12, 2012 4:23 PM

Answers

  • There are no side effects.  It is almost always best to have you cursors declared local.  The only thing that having your cursor declared global does is allow a trigger or stored proc to reference a cursor that was not declared in that procedure or trigger.  So, for example, if you did something like

    -- Create Procedure
    create procedure fooDemoGlobalCursor as 
    begin
    Select 'In Proc, can see and use cursor g because it is global'
    fetch g
    Select 'Leaving Proc'
    end
    go
    
    declare g cursor global static for select * from sys.indexes
      order by object_id, index_id;
    open g
    Select 'Fetch first row from cursor'
    fetch g
    Select 'Call proc to fetch second row'
    exec fooDemoGlobalCursor
    Select 'Fetch third row from cursor'
    fetch g
    
    -- Now close cursorTom
    close g
    deallocate g
    
    go
    -- Cleanup
    drop procedure fooDemoGlobalCursor

    Then the stored proc can see and use the cursor named g even though it doesn't declare or open it.  As long as you are not doing something like that - referencing cursors that aren't declared declared locally - you should not have any problems.

    Tom

    • Proposed as answer by Peja Tao Tuesday, March 13, 2012 5:34 AM
    • Unproposed as answer by JediSQL Tuesday, March 13, 2012 6:04 AM
    • Marked as answer by Kalman TothModerator Monday, October 07, 2013 9:56 PM
    Monday, March 12, 2012 5:17 PM
  • I suggest to go with the LOCAL and also use the best practice and in case you do need to use a cursor (should be very rare occurrence) explicitly define all its options as shown in

    http://social.technet.microsoft.com/wiki/contents/articles/19670.t-sql-useful-links.aspx#Cursors

    This way that setting will not be important. But in my opinion, LOCAL is much better default option.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, September 25, 2013 7:06 PM

All replies

  • You may need to test each of your queries that use cursors for the possible side effects, but in general it's a good idea.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 5:17 PM
  • There are no side effects.  It is almost always best to have you cursors declared local.  The only thing that having your cursor declared global does is allow a trigger or stored proc to reference a cursor that was not declared in that procedure or trigger.  So, for example, if you did something like

    -- Create Procedure
    create procedure fooDemoGlobalCursor as 
    begin
    Select 'In Proc, can see and use cursor g because it is global'
    fetch g
    Select 'Leaving Proc'
    end
    go
    
    declare g cursor global static for select * from sys.indexes
      order by object_id, index_id;
    open g
    Select 'Fetch first row from cursor'
    fetch g
    Select 'Call proc to fetch second row'
    exec fooDemoGlobalCursor
    Select 'Fetch third row from cursor'
    fetch g
    
    -- Now close cursorTom
    close g
    deallocate g
    
    go
    -- Cleanup
    drop procedure fooDemoGlobalCursor

    Then the stored proc can see and use the cursor named g even though it doesn't declare or open it.  As long as you are not doing something like that - referencing cursors that aren't declared declared locally - you should not have any problems.

    Tom

    • Proposed as answer by Peja Tao Tuesday, March 13, 2012 5:34 AM
    • Unproposed as answer by JediSQL Tuesday, March 13, 2012 6:04 AM
    • Marked as answer by Kalman TothModerator Monday, October 07, 2013 9:56 PM
    Monday, March 12, 2012 5:17 PM
  • @ Peja Tao:

    I don't think 13 hours is really a good amount of time to collect a good sampling of "battle stories" as I requested.  If you mark this as answered, everyone will ignore it, and a good body of knowledge will not be accumulated.  The way I phrased my inquiry, there should not be "an answer," just contributions.  Just because one guy says "no problem, go ahead," that doesn't mean that no one has ever had any problems whatsoever.


    Dan Jameson
    Manager SQL Server DBA
    CureSearch for Children's Cancer
    http://www.CureSearch.org

    Tuesday, March 13, 2012 6:11 AM
  • Hello Dan,

    I know this is an old thread, but one that I was curious about nonetheless. I, too, am facing the decision of setting our databases to use the CURSOR_DEFAULT LOCAL option. The code that is in the main database is old, and I am concerned that best practices have been largely ignored over the years. In your case, were you able to carry out your plan? Did you run into any issues that could cause a showstopper?

    I like the idea of collaboration, especially in this forum, since I am flying solo at my current position. Hearing opinions of like-minded professionals can sometimes prove invaluable.


    W. Karl Lambert Sr. SQL Server Database Administrator Business Intelligence Developer

    Wednesday, September 25, 2013 5:42 PM
  • I suggest to go with the LOCAL and also use the best practice and in case you do need to use a cursor (should be very rare occurrence) explicitly define all its options as shown in

    http://social.technet.microsoft.com/wiki/contents/articles/19670.t-sql-useful-links.aspx#Cursors

    This way that setting will not be important. But in my opinion, LOCAL is much better default option.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, September 25, 2013 7:06 PM
  • Karl,

    I have not had any significant issues.  We have been switching databases to LOCAL in conjunction with builds that have significant testing.  Of course, soon after we switched one of our main databases, I ran into one cursor that needed to be GLOBAL.  But it was in a DBA managed process, not an end-user application, so I was able to recover quickly.  So, so far for us it has been fine.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    http://www.ChildrensOncologyGroup.org

    Thursday, September 26, 2013 6:57 PM