Internal architecture of cursors

Question

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?

Answer 1

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

Forward-only

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.

Static

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

Keyset

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

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.