LOCAL and GLOBAL Cursor Scope in SQL Server
Objective: Explain cursor name scope and distinguish scope from lifetime.
Scope determines where a cursor name can be referenced. Lifetime determines how long its underlying cursor remains allocated. They are related, but they are not identical.
Prefer an explicit LOCAL declaration
DECLARE employee_cursor CURSOR
LOCAL FAST_FORWARD
FOR SELECT EmployeeID, EmployeeName
FROM #Employees
ORDER BY EmployeeID;A LOCAL cursor name is available in the batch, stored procedure, or trigger where it is declared. If its references do not escape that scope, it is implicitly deallocated when the scope ends. Passing a reference through a cursor OUTPUT parameter can extend the underlying cursor's lifetime.
GLOBAL means connection-wide
A GLOBAL cursor name can be referenced by other batches or procedures on the same connection. It is not shared with every SQL Server user. Explicitly deallocate it when finished; otherwise it can remain allocated until the connection ends. Do not confuse an allocated cursor with an open cursor: CLOSE can close it without removing its name.
If both scopes contain the same cursor name, an unqualified reference resolves to the local one. A statement such as OPEN GLOBAL employee_cursor; explicitly identifies the global cursor.
Do not rely on the database default
If neither LOCAL nor GLOBAL is specified, the database's cursor default setting determines scope. Naming the intended scope in the declaration makes the code easier to move between databases.
Batch boundaries matter
Keep a LOCAL cursor's declaration, use, and cleanup in one batch. A GO separator after declaration ends the batch and can remove the local cursor before the next command uses it. A local scalar variable also cannot be carried across GO.
Global scope is not intrinsically a different query plan or a guaranteed larger allocation. Its risk is a longer, less obvious lifetime and interference between routines sharing the connection. Use it only for a deliberate connection-level contract.