How to: Create Less Frequently Used Indexes
If you want to create an index that you use infrequently, for example, for a special purpose, you can store it in nonstructural compound index (.cdx) files, which can contain multiple indexes. Included for backward compatibility, you can also store a single temporary or less frequently used index in a standalone index (.idx) file. For more information about index files for storing infrequently used indexes, see Visual FoxPro Index Files.
You can create indexes in nonstructural .cdx files and standalone .idx files using the Visual FoxPro language.
Indexes in Nonstructural Compound Index Files
For an existing table, you can create an index in a nonstructural .cdx file using the INDEX command with the TAG and OF clauses or by copying from one or more standalone index (.idx) files using the COPY INDEXES command.
To create an index in a nonstructural .cdx index file
Use the INDEX command to specify an index expression.
Include the TAG clause to specify the name, or tag, for the index.
Include the OF clause to specify the name of the nonstructural .cdx file.
For example, the following code opens the Employee table in the Visual FoxPro sample database, TestData, and creates an index based on the Title field. The INDEX command includes the TAG clause to specify a name, or tag, for the index and the OF clause to store the index in a nonstructural .cdx file named QRTLYRPT with the .cdx file name extension:
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Employee
INDEX ON Title TAG Title OF QRTLYRPT
For more information, see OPEN DATABASE Command, USE Command, and INDEX Command.
To create an index in a nonstructural .cdx file from an .idx file
Use the COPY INDEXES command to specify one or more names of .idx files. To include each index key from all open .idx files, use the ALL keyword.
Include the TO clause to specify the name of the nonstructural .cdx file.
For more information, see COPY INDEXES Command.
Indexes in Standalone Index Files
For an existing table, you can create a single-key index in a standalone .idx file using the INDEX command with the TO clause or by copying an index from a compound index (.cdx) file using the COPY TAG command.
Tip
To create a small and quickly accessible index file, include the COMPACT clause. However, to create an .idx file that is compatible with the FoxBASE+® and FoxPro® version 1.0 index formats, omit the COMPACT clause.
Note
By default, .cdx files are always compact.
To create a single-key index
Use the INDEX command to specify the index expression.
Include the TO clause to specify the name of the .idx file.
Include the COMPACT clause.
For example, the following code opens the Orders table in the Visual FoxPro sample database, TestData, and creates an index based on the Order_Date field. The TO clause specifies OrdDate as the name of the .idx file. The SET ORDER command sets the order of the table to the OrdDate file, and the BROWSE command opens a browse window that displays the records organized by the Order_Date field:
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Orders
INDEX ON Order_Date TO OrdDate COMPACT
SET ORDER TO OrdDate
BROWSE
For more information, see OPEN DATABASE Command, USE Command, INDEX Command, SET ORDER Command, and Browse Window.
You can also create a standalone index from an index in a .cdx file using the COPY TAG command.
To create a single-key index from a .cdx file
Use the COPY TAG command to specify the name of the index in the .cdx file.
If needed, include the FROM clause to specify the name of the .cdx file.
Include the TO clause to specify the name of the .idx file.
Include the COMPACT clause.
For example, suppose that one of the indexes in a structural .cdx file is used only for quarterly or annual reports. You can transfer this index from the .cdx file to an .idx file. The following code opens the Employee table in the Visual FoxPro sample database, TestData, and creates a single-key index based on the index named Birth_Date in the structural .cdx file associated with the Employee table. The single-key index is stored in an .idx file named BirthDate.
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Employee
COPY TAG Birth_Date TO BirthDate COMPACT
Note
When a table is open, any associated structural .cdx file is also open.
Tip
After you create the .idx file from the .cdx file, you should delete the tag from the .cdx file. For more information about deleting indexes, see How to: Delete Indexes (Visual FoxPro).
For more information, see OPEN DATABASE Command, USE Command, and COPY TAG Command.
See Also
Tasks
How to: Create Indexes (Visual FoxPro)