Home / Questions / 172749
Accepted
Sergio AG
Asked: 2020-06-13 16:18:56 +0800 CST 2020-06-13 16:18:56 +0800 CST

Declaration of T-SQL cursors

  • 772

My question is if in the declaration of the cursor we do not specify any parameter of its behavior, such as SCROLLor LOCALor READ_ONLY, what value will it take by default?

That is, if I write this

DECLARE nombreCursor CURSOR FOR sentencia

What parameter will it take by default?

sql

1 Answers

  1. Best Answer

    In the documentation there is an interesting note:

    note

    If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option.

    The default contemplates two values LOCAL​​and GLOBAL. This is configured at the level databasein the following way (we use as an example Tempdb):

    ALTER DATABASE TEMPDB SET CURSOR_DEFAULT LOCAL;
ALTER DATABASE TEMPDB SET CURSOR_DEFAULT GLOBAL;

    To know which is the default or to be more correct, which is the active default configuration, one way is to do the following:

    -- 1 si es LOCAL, 0 si es Global
select DATABASEPROPERTYEX('TEMPDB', 'IsLocalCursorsDefault')

    Another way is to create a cursor dummyand see the properties of it

    DECLARE cursor_name CURSOR  FOR SELECT 1 AS 'ID' 
SELECT properties FROM sys.dm_exec_cursors(@@spid);
DEALLOCATE cursor_name;

    The exit:

    properties
---------------------------------------
TSQL | Snapshot | Read Only | Local (0)

    In this example, we can confirm that the defaultconfigured mode isLocal

    • 3