Fast Forward-Only Cursors (ODBC)

  • Article

When connected to an instance of SQL Server 2005, the SQL Native Client ODBC driver supports performance optimizations for forward-only, read-only cursors. Fast forward-only cursors are implemented internally by the driver and server in a manner very similar to default result sets. Besides having high performance, fast forward-only cursors also have these characteristics:

  • SQLGetData is not supported. The result set columns must be bound to program variables.
  • The server automatically closes the cursor when the end of the cursor is detected. The application must still call SQLCloseCursor or SQLFreeStmt(SQL_CLOSE), but the driver does not have to send the close request to the server. This saves a roundtrip across the network to the server.

If a result set contains a text, ntext, or image column, a fast forward-only cursor is implicitly converted to a dynamic cursor and SQL_SUCCESS_WITH_INFO is returned to the application. SQLGetData is enabled for the dynamic cursor.

The application requests fast forward-only cursors using the driver-specific statement attribute SQL_SOPT_SS_CURSOR_OPTIONS. When set to SQL_CO_FFO, fast forward-only cursors are enabled without autofetch. When set to SQL_CO_FFO_AF, the autofetch option is also enabled. For more information about autofetch, see Using Autofetch with ODBC Cursors.

Fast forward-only cursors with autofetch can be used to retrieve a small result set with only one roundtrip to the server. In these steps, n is the number of rows to be returned:

  1. Set SQL_SOPT_SS_CURSOR_OPTIONS to SQL_CO_FFO_AF.
  2. Set SQL_ATTR_ROW_ARRAY_SIZE to n + 1.
  3. Bind the result columns to arrays of n + 1 elements (to be safe if n + 1 rows are actually fetched).
  4. Open the cursor with either SQLExecDirect or SQLExecute.
  5. If the return status is SQL_SUCCESS, then call SQLFreeStmt or SQLCloseCursor to close the cursor. All data for the rows will be in the bound program variables.

With these steps, the SQLExecDirect or SQLExecute sends a cursor open request with the autofetch option enabled. On that single request from the client, the server:

  • Opens the cursor.
  • Builds the result set and sends the rows to the client.
  • Because the rowset size was set to 1 more than the number of rows in the result set, the server detects the end of the cursor and closes the cursor.

See Also

Concepts

Cursor Programming Details (ODBC)

Help and Information

Getting SQL Server 2005 Assistance