I know that cursor isn't very good idea, but I have some questions for internal structure of cursor.

First step in cursor is determining result set which cursor iterate over. So, did cursor execute SELECT query and put the result in some temporary table or it is implemented somehow different?

SQL will take data pages in memory form disc, first time data pages was needed. So, assume that I was executed simple SELECT query manually, and SQL pull data pages in memory. Assume also that SELECT query has identical query in FOR clause. What happened at the end of cursor, when cursor are closed and deallocated? Did cursor free memory from used data pages, or that data pages remain in memory?

  • 1
    It depends on what options are selected in the DECLARE CURSOR. As to memory usage, that's separate from cursors. SQL server tries to keep as many pages from tables in memory as possible (whilst trading off against other possible memory usage) Jan 18, 2013 at 7:24
  • Cursors are not always a bad idea. It depends on the situation. They are not the forte of database systems, but there are times when a cursor can outperform the set based alternative.
    – Thronk
    Sep 24, 2013 at 19:41

1 Answer 1


Depends on the cursor type, as documented in Concepts: Cursors:


A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched ... Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors.


The complete result set of a static cursor is built in tempdb when the cursor is opened. ...


The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.


Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. ...

As for memory use, all data access goes through the buffer pool, see Memory Manager Architecture. There is no 'close' nor 'deallocation' occurring. Data is brought in memory as needed, it may be referenced by multiple queries, and evicted only when more free memory is required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.