Share via

INDEX Command

  • Article

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

Note

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.

Warning

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]
   [COMPACT] [ASCENDING | DESCENDING] [UNIQUE | CANDIDATE] [ADDITIVE]

Parameters

  • 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.

    Warning

    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.

    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.

Remarks

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.

Warning

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.

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.

Example

Example 1

The following example closes all databases with the CLOSE DATABASES command and opens the Visual FoxPro sample database, TestData.dbc, with the OPEN DATABASE command and the Customer table with the USE command.

The INDEX command creates a standalone index (.idx) file named Complist based on the Company field. The CLEAR command clears the Visual FoxPro main window, and the DISPLAY STATUS command displays information about the index file, Complist.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Customer     
INDEX ON Company TO Complist
CLEAR
DISPLAY STATUS

Example 2

The following example closes all databases with the CLOSE DATABASES command and opens the Visual FoxPro sample database, TestData.dbc, with the OPEN DATABASE command and the Customer table with the USE command.

The INDEX command creates a standalone index (.idx) file named CityComp from a substring of the first five characters of the City field and the first six characters of the Company field using the SUBSTR( ) function. This index file orders records in the table primarily according to the City field and secondarily according to the Company field. Visual FoxPro clears the main Visual FoxPro window with the CLEAR command, and the DISPLAY STATUS command displays information about the index file, CityComp.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Customer
INDEX ON SUBSTR(City,1,5) + SUBSTR(Company,1,6) TO CityComp
CLEAR
DISPLAY STATUS

Example 3

The following example closes all databases with the CLOSE DATABASES command, and opens the Visual FoxPro sample database, TestData.dbc, with the OPEN DATABASE command and the Customer table with the USE command.

The INDEX commands create a structural compound index (.cdx) file with two tags: one based on the Address field named Address and one based on the Company field named Company. If a structural compound index file was previously created for the table, the two tags are added to the existing file.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Customer     
INDEX ON company TAG company 
INDEX ON address TAG address
CLEAR
DISPLAY STATUS

See Also

Reference

CANDIDATE( ) Function

PRIMARY( ) Function

UNIQUE( ) Function

ATAGINFO( ) Function

CDX( ) Function

COPY INDEXES Command

COPY TAG Command

DELETE TAG Command

DESCENDING( ) Function

FOR( ) Function

INDEXSEEK( ) Function

KEY( ) Function

MDX( ) Function

NDX( ) Function

ORDER( ) Function

SORT Command

SYS(14) - Index Expression

SYS(21) - Controlling Index Number

SYS(22) - Controlling Tag or Index Name

SYS(2021) - Filtered Index Expression

TAG( ) Function

TAGCOUNT( ) Function

Other Resources

Commands (Visual FoxPro)

Working with Table Indexes