Creates an index file containing one or more indexes for displaying and accessing table records in a specified order.


You cannot create primary indexes with the INDEX command. If you want to create a primary index using the Visual FoxPro language, use the SQL CREATE TABLE or ALTER TABLE commands.

Caution noteCaution

Replacing an index key field changes the relative position for the current record in the index. Therefore, use caution if you perform an operation such as REPLACE ALL or SCAN ... ENDSCAN while changing the index for the scope. This also applies to REPLACE operations on indexes created with a FOR clause.

INDEX ON eExpression TO IDXFileName | TAG TagName [BINARY]
   [COLLATE cCollateSequence] [OF CDXFileName] [FOR lExpression]



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.


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 noteCaution

Avoid using RECNO( ) in the index expression for a table buffered cursor. Because RECNO( ) changes for new records when they are committed by TABLEUPDATE( ), index corruption could occur.

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.


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.


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.


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.


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


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.


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.


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.


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.


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.


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.


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


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.


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


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.

Expand imageRemarks

Expand imageExample

Expand imageSee Also