eExpression

Specifies an index expression that determines the order in which Visual FoxPro displays and processes records. eExpression can be a simple expression, such as the name of one or more fields from the current table, or a more complex expression containing Visual FoxPro functions, constants, and so on. Visual FoxPro generates index keys based on eExpression and stores them in an index file that you specify. The index file does not affect the physical order of how records are stored in the table.

Note:
For a standalone index (.idx) file, the length of an index key must be between 1 and 100 characters. For a compound index (.cdx) file, the length of an index key must be between 1 and 240 characters.

With some collating sequences, or when using double-byte character set (DBCS) characters, each character in the index expression uses two characters in the index key. In these cases, the length of the index expression is limited to 50 or 120 characters, respectively.

Caution:
Using RECNO(В ) in the index expression for a table buffered cursor corrupts the index and causes an endless loop.

For more information about index expressions, see Index Creation Based on Expressions and Considerations for Creating Index Expressions.

TO IDXFileName

Specifies the name of a standalone index (.idx) file for storing a single index key as generated by eExpression.

Tip:
You can override the default index file name extension by including a different extension or by changing the default index extension in the Visual FoxPro configuration file. When creating index files, observe standard Windows rules for naming files, which include long file names.

For more information about index files, see Visual FoxPro Index Files.

TAG TagName

Specifies the name, or tag, for the index generated by eExpression and stored in a compound index (.cdx) file. Tag names must begin with a letter or an underscore (_) and can consist of any combination of up to 10 letters, digits, or underscore characters.

Note:
The number of tags in a .cdx file is limited only by available memory and disk space. If a .cdx file for the table already exists and is open, issuing INDEX with TAGВ TagName adds a tag to the open .cdx file. If you create an index tag without specifying the name of the index file, Visual FoxPro adds the tag automatically to the table's structural .cdx file.

For more information about index files, see Visual FoxPro Index Files.

[COLLATE cCollateSequence]

Specifies a collation sequence other than the default setting, MACHINE. cCollateSequence must be a valid Visual FoxPro collation sequence. For more information about setting collation sequences, see Optimizing International Applications and SET COLLATE Command.

[OF CDXFileName]

Specifies the name of a nonstructural compound index (.cdx) file for storing the index, or tag, as generated by eExpression. Omitting this clause creates a structural .cdx file, while including this clause creates a nonstructural .cdx file. For more information about index files, see Visual FoxPro Index Files.

[FOR lExpression]

Specifies a filter expression that selects only those records that match the filter expression for display and access.

Tip:
If lExpression can be optimized, Rushmore technology optimizes the FOR clause in the INDEXВ command. For best performance, use an expression that can be optimized in the FOR clause. For more information, see SET OPTIMIZE Command and Using Rushmore Query Optimization to Speed Data Access.

For more information about filter expressions, see How to: Filter Data.

[COMPACT]

Creates a compact index (.idx) file. Compact .idx files are small and more quickly accessible.

Note:
When creating a compound index (.cdx) file, it is not necessary to include COMPACT. Compound index files are always compact.

For more information, see How to: Create Less Frequently Used Indexes.

[ASCENDING | DESCENDING]

Specifies an order for displaying and accessing records indexed by a compound index (.cdx) file. ASCENDING specifies an ascending order for displaying and accessing records. By default, Visual FoxPro displays and accesses records in ascending order. However, you can include ASCENDING as a reminder of how records are displayed. DESCENDING specifies a descending order for displaying and accessing records.

Note:
You cannot use DESCENDING when creating standalone index (.idx) files; however, you can specify a descending order for an .idx file using the SET INDEX and SET ORDER commands. For more information, see SET INDEX Command and SET ORDER Command.

[UNIQUE | CANDIDATE]

Creates a unique or candidate index. UNIQUE stores the matching index key only for the first record that matches the specified index expression. The index key is stored as the only key in a standalone (.idx) file or as an index tag in a compound index (.cdx) file. Any other index keys for records that match the index expression are excluded from the index file.

Note:

Using UNIQUE does not prevent duplicate records from being entered in the table. It only prevents duplicate index keys from being added to the index file. When a duplicate record is changed so that its index key is changed for an active UNIQUE index or index tag, the index or index tag is updated. However, Visual FoxPro cannot display or access the next duplicate record with the original index key until you use the REINDEX command to reindex the file. For more information, see REINDEX Command.

Using UNIQUE is identical to executing SET UNIQUE ON before issuing INDEX or REINDEX. For more information, see SET UNIQUE Command. CANDIDATE stores an index tag to a structural compound index (.cdx) file only; otherwise, Visual FoxPro generates an error message.

Note:
Candidate indexes do not permit duplicate values in fields. If you create a candidate index for one or more fields that contain duplicate values, Visual FoxPro generates an error.

For more information about candidate indexes, see Visual FoxPro Index Types. For more information about index files, see Visual FoxPro Index Files.

[ADDITIVE]

Keeps open any previously opened index files. Omitting the ADDITIVE clause closes any previously opened index files except the structural compound index (.cdx) files.

Note:
The number of index files that you can keep open is limited only by memory and system resources. In Visual FoxPro, the FILES setting in the Windows Config.sys configuration file determines the total number of files you can open.

[BINARY]

Creates a binary index. For more information about binary indexes, see Visual FoxPro Index Types.

Note:
When specifying an index expression for eExpression, you must specify a valid logical expression that does not evaluate to a null value. If the index expression for a binary index is changed so that it evaluates to a null value, Visual FoxPro generates an error.

When using binary indexes, you cannot use the FOR clause to specify a filter expression or the ASCENDING, DESCENDING, UNIQUE, or CANDIDATE keywords. Visual FoxPro does not support the SET ORDER command when setting to a binary index tag. If you attempt to set order to a binary tag, Visual FoxPro generates an error, and the current order remains at its prior setting. Visual FoxPro does not support SEEK operations or standalone single-key (.idx) indexes with binary indexes.

A structural .cdx file can become dissociated from its table if the index file cannot be located, is deleted, or renamed. When you open a table that has a dissociated structural .cdx file, a dialog box appears. When you click Cancel in the dialog box, the table does not open.

Caution:
Clicking Ignore opens the table; however, it also removes the table header flag that indicates that an associated structural (.cdx) file exists.

Tip:
To reassaociate the structural .cdx file, issue the following command and specify the name of the table as TableName and the name of the dissociated structural .cdx file as CDXFileName.

В	Copy Code
USE TableName INDEX CDXFileName

If the table has been modified, make sure to index the table again.

To report the number of records indexed during the indexing process, set the SET TALK command to ON. To specify the record interval displayed during indexing, use the SET ODOMETER command. For more information, see SET TALK Command and SET ODOMETER Command.

To obtain information about open index files, use the DISPLAY STATUS Command. DISPLAY STATUS lists the names of all open index files, their types, their index expressions, and the name of the master, or controlling, index file or tag. The number of index files that you can open is limited only by memory and system resources.