Using Other Index Types

In addition to the most common index — the compact compound structural .cdx index — Visual FoxPro supports two other types of index files: the non-structural .cdx, and the stand-alone .idx index. Non-structural .cdx indexes are used for less frequently used multiple-key tags. Stand-alone, or .idx indexes are used for temporary or infrequently used single-key indexes, and are available primarily for backward compatibility.

The following table is a summary of the three index types, how they are named, the number of keys they can contain, and the character limitations for each.

Visual FoxPro Index Types

Using Non-Structural .cdx Indexes

A non-structural .cdx index is useful when you want to create multiple index tags for a special purpose, but don't want to burden your application with maintaining these indexes on an ongoing basis. For example, your application may have a special set of reports that analyzes data based on fields not normally indexed. Your application program can create a non-structural .cdx index with the necessary index tags, run the special reports, then delete the non-structural .cdx file.

To create a non-structural .cdx index tag

• Use the TAG and OF clauses with the INDEX command.

You use the OF clause with the INDEX command to direct Visual FoxPro to store the tag in a file other than the structural .cdx index file for the table. For example, the following command creates tags called title and hire_date on the employee table and stores them in a non-structural .cdx file named QRTLYRPT.CDX:

USE employee
INDEX ON title TO TAG title OF QRTLYRPT
INDEX ON hire_date TO TAG hiredate OF QRTLYRPT

Using Stand-Alone Indexes

The stand-alone index file, based on a single key expression, is stored as an .idx file. In contrast to .cdx indexes, which can store multiple key expressions, the .idx index stores only a single key expression.

You typically use stand-alone indexes as temporary indexes, creating or re-indexing them right before you need them. For example, you may have an index that you use only for a quarterly or annual summary report. Rather than include this infrequently used index in the structural .cdx, where it would be maintained every time you use the table, you can create a stand-alone .idx index. You can create as many .idx files as you want for a particular table.

To create a stand-alone .idx index

• Use the COMPACT clause of the INDEX command.

  -or-

• Use the COPY TAG command.

Using the INDEX command with the COMPACT clause creates a new stand-alone index in a small, quickly accessed index file. You can omit the COMPACT clause if you want to create a non-compact stand-alone .idx file for compatibility with the older FoxBASE+® and FoxPro® version 1.0 index formats.

The following code creates a stand-alone .idx file on order_date in the orders table, sets the order to the new index, then opens a Browse window showing the orders in order_date sequence:

USE ORDERS
INDEX ON order_date TO orddate COMPACT
SET ORDER TO orddate
BROWSE

You can use the COPY TAG command to generate a stand-alone index file from an index tag in an existing .cdx file. For example, you may find that one of the indexes you currently maintain in the structural .cdx is used only for quarterly or annual reports. The following code creates a stand-alone index from a tag birth_date in the employee table:

COPY TAG birth_date to birthdt COMPACT

After you've created a stand-alone index from a tag in a .cdx file, you'll typically delete this now unneeded tag from the .cdx file. The next section describes deleting an index.

See Also

Setting Record Order at Run Time | Deleting an Index | Working with Records | Index Creation Based on Expressions | Creating One Index | Ordering by Multiple Fields | Index Creation for Tables