Visual FoxPro Index Files

When you create an index, you supply an expression, which can contain table field names, that specifies how table records should be organized. Visual FoxPro creates an index key for each record in the table based on the index expression and stores the index keys in an index file. The index file contains and maintains pointers to records in the table (.dbf) file and is organized according to the index key values. An index file is separate from but associated with a .dbf file.

Visual FoxPro supports the following types of index files: structural compound index (.cdx), nonstructural compound index (.cdx) files, and standalone index (.idx) files. The structural and nonstructural .cdx files can contain multiple indexes, which have names, or tags, that identify them, while the standalone .idx file contains only a single index.

The following diagram shows a .cdx file with three index names, or tags. Two of the index tags, emp_id and last_name, represent indexes based on single fields. The index tag, cntry_last, organizes records using a simple two-field index expression.

A .cdx file containing multiple tags that represent multiple ordering scenarios for records


cdx Tags Order Scenarios

The following table summarizes the index file types in Visual FoxPro, how they are named, the number of index keys they can contain, and the character limitations for each.

Visual FoxPro Index File Types
Index file type Description Contains Limits

Structural compound index (.cdx) files

Opens and closes with the table automatically.

Uses same base name as the table file name.

Multiple index keys

240-character limit on evaluated expression

Nonstructural compound index (.cdx) files

Must be opened explicitly.

Uses a name different from the base table name and is defined by the user.

Multiple index keys

240-character limit on evaluated expression

Standalone index (.idx) files

Must be opened explicitly.

Uses a name different from the base table name and is defined by the user.

Single index key

100-character limit on evaluated expression

Structural Compound Index Files

When you create an index for a table, Visual FoxPro automatically creates a structural .cdx file to store the index. The term "structural" refers to the fact that Visual FoxPro treats the structural .cdx file as an intrinsic part of the table, opens and closes the structural .cdx file automatically when you open and close the table, and maintains the structural .cdx file automatically when you add, change, or delete table records. If a Visual FoxPro table has an index file associated with it, it is typically a structural .cdx file. A structural .cdx file always has the same base name as the table (.dbf) file name.

The structural .cdx file can contain multiple indexes in the same file. It is recommended that you use the structural .cdx file for indexes you frequently use and need to maintain on a regular basis, such as those used to organize records for daily viewing, data entry, Rushmore Query Optimization, or frequently printed reports.

NoteNote

For a given table, Visual FoxPro stores primary and candidate indexes in the structural .cdx file associated with the table (.dbf) file. You cannot store primary and candidate indexes in other .cdx files, such as nonstructural .cdx files, nor is it possible to use standalone index (.idx) files for primary and candidate indexes because the index file should always open whenever the associated table is opened.

Nonstructural Compound Index Files

Nonstructural .cdx files store multiple indexes that are not frequently used. You can use a nonstructural .cdx file when you want to create multiple indexes for a special purpose but do not want your application to maintain these indexes on a continuing basis. A nonstructural .cdx file always has a name that is different from the table (.dbf) file name and is defined by the user.

For example, suppose your application contains a special set of reports that analyzes data based on fields not normally indexed. You can create a nonstructural .cdx file containing the appropriate indexes, run the reports, and then delete the nonstructural .cdx file.

NoteNote

Unlike structural .cdx files that open automatically when the associated table is opened, you must open a nonstructural .cdx file explicitly using the SET INDEX command or the USE command with the INDEX clause. For more information, see SET INDEX Command and USE Command.

Standalone Index Files

Available primarily for backward compatibility, a standalone .idx file stores a single index key that is temporary or less frequently used. You can use standalone (.idx) files as temporary indexes by creating or reindexing them immediately before you use them. A standalone .idx file always has a name that is different from the table (.dbf) file name and is defined by the user.

For example, suppose you have an index that you use only for quarterly or annual summary reports. Instead of including this index in the structural .cdx file, where it is maintained every time you use the table, you can create a standalone .idx file to store this index. You can create as many .idx files as you want for a particular table.

See Also

Community Additions

ADD
Show: