Cursor Models

The subject of cursors confuses many people, at least in part because several cursor models are available and those models have different options and capabilities. We can group these models into three categories: Transact-SQL cursors, API server cursors, and client cursors.

Transact-SQL Cursors

You use Transact-SQL cursors within SQL typically in a stored procedure or in a batch that needs to do row-by-row processing. Transact-SQL cursors use the familiar statements DECLARE CURSOR, OPEN cursor , and FETCH. At times, Transact-SQL cursors have been referred to as "ANSI cursors" because their syntax and capabilities fully implementand go well beyondthe scrollable cursor functionality specified by ANSI SQL-92. The ANSI specification states that scrollable cursors are read-only. This isn't the way applications work, of course, so fortunately SQL Server allows scrollable cursors that can be updated. SQL Server provides backward scrolling, relative and absolute positioning within the result set, and many options regarding sensitivity to changes made by others, as well as positioned updates and deletes. Few other products implement even the ANSI specification for scrollable, read-only cursors, let alone a cursor model as rich as SQL Server's. The cursor functionality of SQL Server is without question the richest in the industry. The term "ANSI cursor" is rarely used anymore because it implies a feature that is commonplace in the industry; the extensive cursor functionality is unique to SQL Server and greatly exceeds that specified by ANSI.

API Server Cursors

The OLE DB provider for SQL Server, the ODBC driver for SQL Server, and the DB-Library programming libraries have special cursor functions that are optimized for cursor operations and better network use between a client application and a server application. For a high-performance application, it is important that there be no excessive conversation between the client and the server. The hallmark of high-quality client/server computing is to have the client issue a single, brief command and then have the server respond. If every row fetched in a cursor had to be individually requested over the network and had to provide all the metadata to describe the row fetched , performance would be abysmal.

The OLE DB provider and the ODBC driver that Microsoft provides for SQL Server fully implement these special cursor commands, as does the DB-Library programming interface. Higher level interfaces, such as RDO and ADO, also implement these functions. These capabilities have the cumbersome names "ODBC server cursors," "DB-Library server cursors," and "OLE DB server cursors," so they're all generally referred to in the documentation as "API server cursors"

API server cursors enable smarter communication between the calling client application and the SQL Server back end. Central to this is the interface's ability to specify a "fat" cursor. That is, instead of thinking of a cursor as pointing to a single row, imagine the cursor pointing to multiple rows. Take, for example, an application that displays a list box of 20 customers at a time. As the user scrolls through the list box, a FETCH requests the next 20 rows. Or perhaps it fetches the previous 20 rows, the last 20 rows, or the 20 rows thought to be about three-fourths of the way through the result set. The interface is provided by such function calls as SQLFetchScroll for ODBC and IRowset::GetNextRows for OLE DB. These functions make sense within those programming environments, and they are used instead of SQL statements such as DECLARE CURSOR and FETCH. These API functions allow options and capabilities, such as defining the "width" of the cursor (the number of rows), that cannot be expressed using Transact-SQL cursor syntax. Another important performance optimization of API server cursors is that metadata is sent only once for the cursor and not with every FETCH operation, as is the case with a Transact-SQL cursor statement.

Besides offering performance advantages ( especially for operations across a network), API server cursors have other capabilities that Transact-SQL cursors do not have. API server cursors can be declared on a stored procedure if the procedure contains only one SELECT statement. You use API server cursors mostly in application code.

NOTE

---

Many APIs provide access to API server cursors, but in this chapter we'll look at them primarily in the context of Transact-SQL cursor syntax, which is the most widely readable syntax. But when a capability is available only via API server cursors, we'll point that out. The information on Transact-SQL cursors is also relevant to all forms of API server cursors. After reading the information here, you should consult the programming reference for the specific API you use to better understand the capabilities offered .

Client Cursors

If you consider that the job of a cursor is to set a position within a result set, it should be clear to you that this task can be performed (although possibly less efficiently ) within the client application with no assistance from the server. SQL Server originally had no cursor support, and many application designers wrote their own client-side cursor libraries to do this type of operation. While SQL Server 4.2 did not have cursor support in the engine, it offered a rich set of cursor functions for DB-Library so that application programmers would not have to reinvent the wheel. These functions were implemented entirely on the client. No magic was performed, and these functions did nothing that other ISVs couldn't do. But the designers knew from the outset that they eventually wanted cursor support in the engine. They designed the API so that the bulk of the work could eventually be executed remotely at the server and that a new API or substantial changes to applications using the API would not be necessary.

Version 6.0 introduced scrollable cursor support directly in the engine. With this support, applications written using the DB-Library version 4.2 cursor library typically worked without any changes. In fact, they worked quite a bit better because of the server-side assistance that they got. Version 6.0 added an optimized ODBC driver that fully exploited SQL Server's support for scrollable cursors. With database management systems other than SQL Server (most of which do not have scrollable cursors), it's not unusual for an ODBC driver to take on these cursor functions, as DB-Library used to for SQL Server. But with the ODBC driver that ships with SQL Server, cursor operations use the back-end engine's capabilities.

With SQL Server, you tend to make use of cursors on the server. Because client cursors are more of a programming convenience, we won't cover them in much depth. Instead, we'll focus on the server. But be aware that you can still use the OLE DB or ODBC cursor library to do the cursor operations for you (using client cursors) instead of having them processed at the server.

Some people consider client cursors obsolete now that server cursors are available. But client cursors can make a lot of sense when properly used. For example, if you want your user interface to provide forward and backward scrolling but you want to use the more efficient default result set rather than a server cursor, you need a local buffering mechanism to cache the entire result set and provide the scrolling operations. You can write this mechanism yourself, but client cursors already provide it. Also, the Internet has brought new issues and challenges for "stateless" management of data. (That is, the connection to SQL Server is not persistent.) Client cursors can play a major part in these solutions. Although the introduction of server cursors in version 6.0 might have made client cursors obsolete, the Internet will probably drive future work into new and more sophisticated client-cursor solutions.

Default Result Sets

Do not fall into the habit of thinking that every result set is a cursor, at least of the type previously discussed, or you'll inch toward the dark sidealways using cursors. Earlier we said that you can think of a cursor as a named result set. You can think of rows not returned in a cursor as the default result set or as just a result set. A default result set is really not a cursor. You can think of it as a fourth type of cursor if you remember that no cursor functionality at the server is involved.

A default result set is frequently referred to as a firehose cursor in discussions of the cursor capabilities of ODBC. Within the SQL Server ODBC driver, you use the same function call, SQLFetchScroll, to process rows from SQL Server whether they emanate from a cursor operation or simply from a default result set. With ODBC, if you request a cursor that is forward-only and read-only, with a row set size of 1, the driver doesn't ask for a cursor at all. Instead, it asks for a default result set, which is much more efficient than a cursor. The term "firehose" reflects the way that the server blasts rows to the client as long as the client keeps processing them so that there is room in the network buffer to send more results. In Chapter 3, we saw how results are sent from the server to the client, and this is exactly what happens with a firehose cursor. The fact that the same ODBC functions work for both cursor and noncursor results is a nice programming convenience, so we'll continue to use the term "firehose cursor." Just remember that from the SQL Server engine's perspective, it is not a cursor.

API Server Cursors vs. Transact-SQL Cursors

Although this book is not about OLE DB or ODBC programming, cursors are intrinsically related to those programming interfaces. This shouldn't be too surprising, since you know that a key motivation for including cursors in SQL Server was to bridge the impedance mismatch between the set operations of SQL Server and the ISAM operations of traditional programming languages. And traditional programming languages use ODBC or OLE DB (or higher level interfaces that build on them, such as RDO or ADO). For a complete description of API server cursors, see Inside ODBC (Microsoft Press, 1995) by Kyle Geiger. Kyle's discussion is obviously centered on ODBC, but he goes into significant depth to show the interaction between the ODBC calls and the server cursor functions. If you program with ODBC, Kyle's book is a must-read . But even if you use another interface that uses cursor functionality, there is plenty to be gained from Kyle's discussion.

SQL Server provides some specialized cursor functions that are called only internally by the OLE DB provider, the ODBC driver, or DB-Library.

These functions look like stored procedures and have stubs in the sysobjects table, so you might see their names there (for example, sp_cursor ). If you trace the functions going to SQL Server (using SQL Server Profiler, or perhaps a network sniffer), you will see these functions flowing from the client application. In fact, you can execute them directly from ad hoc tools such as the Query Analyzer, although you should not call them directly from your applications.

These functions are not really stored procedures; they are capabilities implemented directly in SQL Server. The code is compiled into sqlservr .exeit is not a stored procedure or an extended stored procedure. We'll refer to such functions as "pseudo_stored procedures." The stubs in sysobjects make cursor execution mimic the execution of a stored procedure so that there is no need for major changes to the tabular data stream (TDS) between client and server. Remember that when server cursor support was implemented in version 6.0, a major goal was to have SQL Server work seamlessly with applications that had been written to use the client cursor functions of DB-Library version 4.2, so implementing cursors without changing the TDS was a clever idea. These functions are not intended, or supported, for direct use by an application. They are invoked directly by the ODBC driver, the DB-Library programming libraries, and the native OLE DB provider for SQL Server.

The functions can also be used directly by an ISV that is trying to provide a gateway to another data source if that ISV wants the gateway to include support for scrollable cursors. The gateway can trap these calls and then mimic them in whatever way makes sense to the final destination. As far as we know, no such gateway yet achieves the level of transparency achieved with OLE DB, ODBC, and DB-Library. (Doing so would be far from trivialwriting a cursor library takes significant work. And such an effort would require technical assistance from Microsoft, since the interface hasn't been supported for third-party development. But since products such as gateways tend to be relatively expensive, high-level capabilities are expected, and such a capability would be highly valued.)

Transact-SQL cursors and API server cursors use the same code within SQL Server, although the Transact-SQL cursors do not directly call the pseudo_stored procedures. Because the procedures exist only for the sake of remoting the work from OLE DB, ODBC, and DB-Library, an SQL statement, which is already executing inside the engine, has no need to call the routines in that way. While Transact-SQL and API server cursors execute the same code and have many similarities, you should not think of them as alternative ways to do the same thing.