|Programming in MS SQL Server 2012|
When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft SQL Server. Statements that operate on cursors use either a cursor name or a cursor variable to refer to the cursor.
DEALLOCATE removes the association between a cursor and the cursor name or cursor variable. If a name or variable is the last one referencing the cursor, the cursor is deallocated and any resources used by the cursor are freed.
Scroll locks used to protect the isolation of fetches are freed at DEALLOCATE. Transaction locks used to protect updates, including positioned updates made through the cursor, are held until the end of the transaction.
* * * * *