Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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) –  Damien_The_Unbeliever Jan 18 '13 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 '13 at 19:41
add comment

migrated from stackoverflow.com Jan 20 '13 at 22:37

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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