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

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.