Chapter 11 -- Cursors

Chapter 11

A relational database such as Microsoft SQL Server is naturally set oriented . This means that a given statement, such as SELECT, returns a set of resultsoften more than one row of data. On the other hand, most programming languages and many applications tend to be ultimately record based . For example, in an application that displays a list of customers, users scroll through the list and periodically drill down to get more detail about a customer and perhaps make some modifications. Then they might proceed to the next customer and do the operation again, and so on, one record at a time.

The incongruity between the set-oriented approach and the record-based approach is sometimes referred to as an impedance mismatch . To deal with this mismatch, SQL Server provides a significant bridge between the two models: cursors.

You can think of a cursor as a named result set in which a current position is always maintained as you move through the result set. For example, as you visually scan names in a printed phone book, you probably run your finger down the entries. Your finger acts like a cursorit maintains a pointer to the current entry. You can think of the entries on the current page of the phone book as the result set from your query. The action of moving your finger through the listings on that page is similar to the action of a scrollable cursor it moves forward and backward or up and down at different times, but it always maintains a current position on a particular row.

SQL Server has different types of cursors that you can choose based on your scrolling requirements and on how insulated you want the cursor to be from data changes made by others. Special cursor libraries in ODBC, OLE DB, and DB-Library have network optimizations that prevent each row fetched in a cursor from incurring the overhead of its own network conversation. Figure 11-1 shows how a cursor works.

click to view at full size.

Figure 11-1. SQL Server cursors bridge set-based and record-based models.

Inside Microsoft SQL Server 7.0
Inside Microsoft SQL Server 7.0 (Mps)
ISBN: 0735605173
EAN: 2147483647
Year: 1999
Pages: 144 © 2008-2017.
If you may any questions please contact us: