Post reply

Why use GLOBAL cursors rather than LOCAL ones?

  • February 17, 2005 at 1:44 pm

    #163691

    Does anybody have a good reason for using GLOBAL cursors (which appear to be the SQL Server 2000 default) rather than LOCAL ones?

    Let me say first of all that I realize using cursors is not a great thing to do if it can be avoided. However, I was just asked by some of my colleagues to investigate a problem they were having with some stored procedures and/or triggers they wrote. Apparently, these were failing because they were attempting to define cursors that were already created.

    Thanks to Books Online, I was able to find a quick solution for them (i.e. just add the LOCAL keyword in the DECLARE CURSOR statement).

    The question was raised, however, why would GLOBAL be the default setting?

  • February 17, 2005 at 1:56 pm

    #542940

    That's a very good question... one that I can't answer. However is is considered best pratice to explicitly declare the cursor as local or global.

  • February 17, 2005 at 1:56 pm

    #542938

    I differ with your statement. From BOL (Update Jan 2004)

    Note  If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server, in which all cursors were global. The default of this option may change in future versions of SQL Server.

    HTH

     


    * Noel

  • February 17, 2005 at 2:10 pm

    #542948

    Noel,

    I think we are both saying the same thing here. I claimed that the default setting was that cursors would be created as GLOBAL.

    This is congruent with your correct statement that the default to local cursor database option defaults to FALSE. Does that make sense?

  • February 17, 2005 at 2:15 pm

    #542951

    What I was trying to say is:

    If your MODEL Database has the 'default to local cursor' ON/TRUE then your cursors are going to be local (Mine are like that). For Databases that were migrated from SQL 7 probably you have that setting as FALSE for backward compatibility reasons (as BOL explained) which is the anwser to your ... why?

    HTH

     

     


    * Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply