Overview of Indexes

An OLE DB index, also known as an index rowset, is a rowset built over an index in a data source. It is generally used in conjunction with a rowset built over a base table in the same data source. Each row of the index rowset contains a bookmark that points to a row in the base table rowset. Thus, the consumer can traverse the index rowset and use it to access rows in the base table rowset. This chapter discusses the interfaces, use, and structure of an index rowset. Index structures comprise either of two types: one or more columns that form a key value, or a column containing a bookmark. An index example is also provided.

For more information on	Go to
Data source	“OLE DB Components” in Chapter 1, “Overview of OLE DB”
Rowset	Chapter 4, “Rowsets”
Bookmark	“Bookmarks” in Chapter 4, “Rowsets”
Structure	“Structure of Index Rowsets” in this chapter
Use of indexes	“Using Index Rowsets” in this chapter
Example provided	“Index Example” in this chapter

Indexes:

CoType TIndex {
   [mandatory] interface IAccessor;
   [mandatory] interface IColumnsInfo;
   [mandatory] interface IConvertType;
   [mandatory] interface IRowset;
   [mandatory] interface IRowsetIndex;
   [mandatory] interface IRowsetInfo;
   [optional]  interface IRowsetChange;
   [optional]  interface IRowsetFind;
   [optional]  interface IRowsetIdentity;
   [optional]  interface IRowsetLocate;
   [optional]  interface IRowsetRefresh;
   [optional]  interface IRowsetScroll;
   [optional]  interface IRowsetUpdate;
   [optional]  interface IRowsetView;
   [optional]  interface ISupportErrorInfo;
};

The primary consumer for the IRowsetIndex interface is a query processor component that uses it during query execution. Although an SQL provider can expose IRowsetIndex by translating IRowsetIndex methods into SQL statements, this is not efficient. Instead, typical consumers of SQL providers should use the ICommand interface as the primary data access mechanism and rely on the query processor in the SQL provider to optimize access to data.

The primary index interface, IRowsetIndex, exposes the functionality required by file access methods based on ISAM and B+-trees. It does not support functionality, such as hashing, required by unordered indexes; R+-trees, required by access methods for spatial data; or signature files, required by access methods for text.

Index rowsets can be used in the following situations:

Reading records efficiently by means of a key, which is one or more columns whose values identify a row. The following are example SQL queries whose execution plans can exploit the capabilities of the IRowsetIndex interface:
- Retrieving rows ordered by some column. For example, SELECT * FROM Table ORDER BY Table.x, where x is indexed.
- Scanning records having a column value within a continuous range. For example, SELECT * FROM Table WHERE Table.x BETWEEN 4 AND 20.
- Computing joins. For example, SELECT * FROM R INNER JOIN S ON R.x = S.x.
- Retrieving distinct rows. For example, SELECT DISTINCT R.x FROM R, where R.x is indexed.
- Positioning at a particular index entry, such as skipping over key entries within a range.
Supporting indexes with multicolumn keys. The values in each column can be in ascending or descending order.
Traversing indexes in ascending or descending order to support ORDER BY clauses.