PRB: SQL Server Cursor Type May Change From Dynamic to Keyset

ID: Q153115

The information in this article applies to:

SYMPTOMS

Under certain circumstances, on a table with few rows, a keyset cursor is created on a SELECT statement with an ORDER BY clause even when a dynamic or a server-side forward_only cursor is specified and all the rules for creating dynamic/forward_only cursors are satisfied.


WORKAROUND

Specify an explicit optimizer hint to use the unique index that you created on the column(s) used in the ORDER BY clause of the SELECT statement.

In ISQL/W run the following script:


create table test(vendor int not null , upc char(12) not null)
go
create unique index ind1 on test(upc)
go
create unique index ind2 on test(vendor)
go
insert test values(20,'ABC Vision')
go
insert test values(10,'XYZ Tech')

In ODBC Test:

  1. FullConnect to the datasource that points to the database that has the TEST table created above.


  2. Call SQLSetScrollOptions and specify SQL_CONCUR_VALUES, SQL_SCROLL_DYNAMIC, and 1.

    You can also use SQL_CONCUR_READ_ONLY, SQL_SCROLL_FORWARD_ONLY, and 2.


  3. SQLExecDirect on "select * from test where vendor=20 and upc>='ABC Vision' order by vendor"

    This will give a SQL_SUCCESS_WITH_INFO and the cursor is changed to keyset type.


If this is not acceptable, you can modify the SELECT statement in step 3 by adding an optimizer hint and avoid the change in cursor type. That is: 

   Select * from test (index=ind2)  where vendor=20 and upc>='ABC Vision'
   order by vendor

If you have more than 300 rows in the table, you might not see this problem. It is worth noting that on a small table there is no significant performance difference between a dynamic and a keyset cursor.

Additional query words: ODBC RDO VB MFC Database Classes 


Keywords          : kbprg SSrvProg 
Version           : 2.65.0201 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 30, 1999