Database Reference
In-Depth Information
Chapter 22
Row-by-Row Processing
It is common to find database applications that use cursors to process one row at a time. Developers tend to think
about processing data in a row-by-row fashion. Oracle even uses something called
cursors
as a high-speed data access
mechanism. But cursors in SQL Server are different. Because data manipulation through a cursor in SQL Server
incurs significant additional overhead, database applications should avoid using cursors. T-SQL and SQL Server are
designed to work best with sets of data, not one row at a time. Jeff Moden famously termed this type of processing
RBAR (pronounced, ree-bar), meaning row by agonizing row. However, if a cursor must be used, then use a cursor
with the least cost.
In this chapter, I cover the following topics:
•
The fundamentals of cursors
•
A cost analysis of different characteristics of cursors
•
The benefits and drawbacks of a default result set over cursors
•
Recommendations to minimize the cost overhead of cursors
Cursor Fundamentals
When a query is executed by an application, SQL Server returns a set of data consisting of rows. Generally,
applications can't process multiple rows together; instead, they process one row at a time by walking through the
result set returned by SQL Server. This functionality is provided by a
cursor
, which is a mechanism to work with one
row at a time out of a multirow result set.
T-SQL cursor processing usually involves the following steps:
Declare the cursor to associate it with a
SELECT
statement and define the characteristics of
the cursor.
1.
Open the cursor to access the result set returned by the
SELECT
statement.
2.
3.
Retrieve a row from the cursor. Optionally, modify the row through the cursor.
4.
Once all the rows in the result set are processed, close the cursor and release the resources
assigned to the cursor.
