Keyset-Driven Cursors Requirements for OLE DB Providers

Transact-SQL keyset-driven cursors can reference remote tables only if the following conditions are met:

• The distributed query must meet the requirements for SELECT statements used in a DECLARE CURSOR statement that declares the keyset-driven cursor. For more information about the Transact-SQL conditions for keyset-driven cursor support, see DECLARE CURSOR.
  
  
• All local tables in the query must have a unique index. The index of the remote table should be exposed through the INDEXES rowset of the IDBSchemaRowset interface.

Index Requirements on OLE DB Providers

SQL Server can use indexes on tables from an OLE DB provider to evaluate certain queries. For this, the provider should expose OLE DB interfaces that allow scanning an index rowset and seek into the base table rowset using bookmarks obtained from the index rowset.

Using the OLE DB provider's indexes has performance benefits only when the index and table rowsets are on the same computer as the instance of Microsoft® SQL Server™. Thus, the Index AS Access Path option should be set only if the data source is on the same computer as SQL Server.

SQL Server can use an OLE DB provider's indexes only if the following conditions are met:

• The provider must support the IDBSchemaRowset interface with the TABLES, COLUMNS, and INDEXES schema rowsets.
  
  
• The provider must support opening a rowset on an index through IOpenRowset by specifying the index name and the corresponding base table name.
  
  
• The Index object must support all its mandatory interfaces: IRowset, IRowsetIndex, IAccessor, IColumnsInfo, IRowsetInfo, and IConvertTypes.
  
  
• Rowsets opened against the indexed base table (through IOpenRowset) must support the IRowsetLocate interface for positioning on a row based off a bookmark.

If the OLE DB provider meets these requirements, the SQL Server administrator can set the Index As Access Path provider option to enable SQL Server to use the provider's indexes to evaluate the queries. By default, SQL Server does not attempt to use the provider's indexes unless this option is set.

Updatable Keyset Cursor Requirements

A remote table can be updated or deleted through a keyset cursor defined on a distributed query. For example:

UPDATE | DELETE remote_table WHERE CURRENT OF cursor_name. 

Here are the conditions under which updatable cursors against distributed queries are allowed:

• The provider should meet the conditions for updates and deletes on the remote table. For more information, see UPDATE and DELETE Requirements for OLE DB Providers.
  
  
• All the cursor operations must be in an explicit user transaction (or multi-statement transaction) with read-repeatable isolation level or serializable isolation level.

The provider must support distributed transactions with the ITransactionJoin interface.