Edit

Share via

Fast Forward-Only Cursors (ODBC)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

When connected to an instance of SQL Server, the SQL Server 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.

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

Cursor Programming Details (ODBC)