Chapter 13 -- Special Transact-SQL Operations: Working with Cursors and Large Objects

Chapter 13

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 results—often 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 cursor—it 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 13-1 shows how a cursor works.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net