SQL Server Magazine

Because Structured Query Language (SQL) is a set-oriented language, SQL Server performs an operation on a row only if that row is a member of the set defined by the WHERE clause in the SELECT, UPDATE, or DELETE statement. A set-oriented language lets you apply a change to all the rows as a group rather than issuing a separate command for each row.

However, the problem with a set-oriented language is that it lacks a concept of order or individuality for the items in the set. An item has only one property—membership in the set. Therefore, you cannot move from the first row in the set to the last row, nor can you retrieve information from a particular row without executing another query that targets that specific row.

Cursors work around this deficiency in SQL by artificially imposing order and individuality on the rows in the rowset that the SQL SELECT statement returns. In this article, I will explain how ADO uses cursors as a storage and access mechanism, and how you can choose the best cursor for your application.

Why ADO Uses Cursors
When your application retrieves rows of data from SQL Server, it needs a place to store the rowset while it processes the information. An ADO Recordset object encapsulates the data and the operations allowed on that data. The Recordset object uses a cursor as a mechanism to organize the temporary storage. The cursor is similar to a dynamic array stored in memory, and the Recordset object is your interface to that array.

Within ADO, cursors have three functions. First, the cursor location determines where to store the rowset while the cursor is open. Second, the cursor type determines movement within the cursor and whether the rowset will reflect users' changes. Third, the cursor's locking type specifies how SQL Server will lock the rows on the server when you want to make changes.

Later in this article, I show you how these three functions interact to affect the application's functionality and the load the application places on the server. First, let's look at each function and its effect on the cursor.

Cursor Location
The cursor location determines whether ADO or SQL Server manages the cursor. The MSDASQL and SQLOLEDB providers manage rowset storage on the client side. SQL Server retrieves the rows from the table and uses either ODBC drivers to send them to MSDASQL or uses the OLEDB interface to send them to SQLOLEDB. In either case, the client-side database drivers allocate virtual memory to hold the rows.

With SQL Server, server-side cursors take up space in the TempDB database. The MSDASQL and SQLOLEDB providers call special functions to tell SQL Server which rows to retrieve and which type of cursor to open. Then, instead of sending the entire rowset to the client, SQL Server stores it in TempDB. The database drivers then retrieve individual rows at the client application's request.

Cursor Types
ADO supports four types of cursors: static, dynamic, keyset, and forward-only. These cursor types vary in how the cursor responds to changes in its row membership and in what directions you can move through the rowset. Table 1 lists each cursor type and its attributes when the data source is SQL Server.

Static Cursors. Static cursors are so named because additions and deletions of rows don't change the list of rows. Furthermore, changes to existing records don't appear. Any change the cursor owner makes through the cursor appears immediately, but the static cursor ignores only modifications by other users until the application refreshes the cursor. In general, you can think of a client-hosted, static cursor as a local copy of the table that's isolated from the rest of the database system.

Client-hosted and server-hosted static cursors let you use the MoveFirst, MoveNext, MovePrevious, MoveLast, and Move methods of the Recordset object to move to any row in the cursor. Also, both cursors let you move to a specific row by setting the Bookmark property on the Recordset. Bookmarks are internal identifiers that the ADO cursor engine maintains and that provide a unique address for each row in the cursor. To move to a specific row, you read the Bookmark property and store the value in an application variable. If you set the Bookmark property to the value stored in the variable, the cursor position moves back to the row. You can use Bookmarks to create a list of specific rows.

In addition, client-hosted static cursors let you move to a specific row by setting the AbsolutePosition property equal to the row number. The valid values for the property are 1 to the number of rows stored in the Rowcount property. An important consideration when you use the AbsolutePosition property is that a row's position within the rowset may change depending on inserts and deletes. For example, if you delete row 4, then row 5 becomes the new row 4. Therefore, SQL Server documentation recommends using Bookmarks instead of row numbers, but absolute positioning works well for read-only cursors.

Forward-Only Cursors. Forward-only cursors are similar to static cursors except that forward-only cursors let you scan only from the first to the last rows in the cursor. You can update rows, insert new rows, and delete rows, but you can't move backward. Only the MoveNext method works for the forward-only cursor.

In SQL Server 7.0, forward-only cursors are a special case. Whereas most database APIs treat forward-only cursors as a specific kind of cursor, SQL Server 7.0 considers them a kind of cursor behavior, meaning that the cursor defines how you can scroll through the data. Thus, when you ask SQL Server 7.0 to create a server-side, forward-only cursor, you get a dynamic cursor that supports scrolling in only one direction.

Keyset Cursors. With keyset cursors, the row membership and row order are fixed when you open the cursor. As with a static cursor, your application can move back and forth between rows. Unlike static cursors, however, keyset cursors let you see changes other programs make and changes your program makes. Remember that client-hosted cursors are essentially local copies of the data; therefore, a client-hosted keyset cursor isn't useful. In my tests, ADO always changes these cursors into client-hosted static cursors.

Keyset cursors present three special cases. First, when a user deletes a row, SQL Server marks the row as deleted and it becomes inaccessible. Second, if a user changes a row so that it no longer matches the cursor's WHERE clause criteria, SQL Server in effect removes the row from the cursor as if the user had deleted it. The row still exists in the table, but it is inaccessible. Third, if another user inserts a row into the table, SQL Server doesn't add it to the cursor, but if you add a row through the cursor, it appears at the end of the cursor.

Dynamic Cursors. If your application requires immediate access to all changes, regardless of who makes the changes, you need to use dynamic cursors. Dynamic cursors let your application move in any direction through the cursor, respond immediately to changes in rowset membership, and show all users' changes to all rows. Choose dynamic cursors if multiple users insert, update, and delete rows in the database at the same time.

Dynamic cursors are flexible, but they don't support absolute positioning. Because dynamic cursors respond to all membership changes, they don't provide a way for you to identify, for example, the 10th row in the cursor. Moreover, dynamic cursors don't support bookmarks because the cursor can't guarantee that the unique identifier stored on the client will be valid the next time the application wants to use that row. SQL Server maintains one row in its buffer, and it re-executes part of the query to find the next row. Thus, dynamic cursors use more server resources than other cursors. Although dynamic cursors are powerful and extremely flexible, use them only when you need to.

   Prev. page   [1] 2     next page