OLE DB Provider Categories

The following is a categorization of OLE DB providers based on their capabilities from a SQL Server distributed querying standpoint. As defined, these are not mutually exclusive; a given provider may belong to more than one of the following categories:

• SQL Command Providers
• Index Providers
• Simple Table Providers
• Non-SQL Command Providers

SQL Command Providers

Providers that support the Command object with a SQL standard dialect recognized by SQL Server belong to this category. The specific requirements for a given OLE DB provider to be treated as a SQL Command provider by SQL Server are:

• The provider must support the Command object and all of its mandatory OLE DB interfaces: ICommand, ICommandText, IColumnsInfo, ICommandProperties, and IAccessor.
• The SQL dialect supported by the provider must be at least ODBC Core level on the ODBC conformance scale or SQL 92 Entry level on the SQL-92 conformance scale. The dialect must be reported by the provider through the DBPROP_SQLSUPPORT property.

Examples of SQL Command providers are the Microsoft OLE DB Provider for SQL Server and the Microsoft OLE DB Provider for ODBC.

Index Providers

Index providers support and expose indexes according to OLE DB and allow index-based lookup of base tables. The specific requirements for a given OLE DB provider to be treated as an Index provider by SQL Server are:

• 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 the above requirements, users can set the Index As Access Path provider option to force SQL Server to use the provider’s indexes to evaluate queries. By default, SQL Server does not attempt to use the provider’s indexes unless this option is set.

Note SQL Server supports various options that influence how SQL Server accesses an OLE DB provider. The Linked Server Properties dialog box in SQL Server Enterprise Manager can be used to set these options.

Simple Table Providers

These are providers that expose the opening of a rowset against a base table through the IOpenRowset interface. Such providers are neither SQL Command providers nor Index providers; rather, they are the simplest class of providers that SQL Server distributed queries can work with.

Against such providers, SQL Server can perform only table scans during distributed query evaluation.

Non-SQL Command Providers

Providers that support the Command object and its mandatory interfaces but do not support a SQL standard dialect recognized by SQL Server fall into this category.

Two examples of Non-SQL Command providers are the Microsoft OLE DB Provider for Indexing Service and the Microsoft Windows NT® Active Directory Service Interfaces (ADSI) OLE DB provider.