Summary

This chapter described server cursors in detail. Cursors are important tools that help you bridge the impedance mismatch between the set-oriented world of SQL Server and traditional record-oriented programming languages. When used properly, cursors are invaluable. When overused or used inappropriately, they can cause you to have a bad experience with SQL Server. SQL Server provides fully scrollable, updatable cursors. Other mainstream products typically provide only forward-only cursors; if they do allow full scrolling, they do not allow updates. The cursor capabilities in SQL Server are currently far richer than those of any other RDBMS. SQL Server cursors implement and go well beyond the functionality in ANSI SQL-92.

SQL Server has four main cursor models: static, keyset, dynamic, and forward-only. Cursors can be accessed via syntax used directly in Transact-SQL or via function calls used in the programming interfaces to SQL Server (ODBC, OLE DB, and so on). These two types of cursors are called Transact-SQL cursors and API server cursors. Transact-SQL cursors are typically used if a batch or stored procedure must do some row-by-row processing. API server cursors are often appropriate for scrolling applications. Cursors allow positioned updates and deletes. They can use optimistic concurrency control or locking (also known as pessimistic concurrency control). This chapter focused on the Transact-SQL syntax, but the key points are relevant no matter what cursor interface you use with SQL Server.