INDEX Command

  • Article

Creates an index file to display and access table records in a logical order.

INDEX ON eExpression TO IDXFileName | TAG TagName 
   [ COLLATE cCollateSequence ] [OF CDXFileName] [FOR lExpression]
   [COMPACT] [ASCENDING | DESCENDING] [UNIQUE | CANDIDATE] [ADDITIVE]

Parameters

  • eExpression
    Specifies an index expression that can include the name of a field or fields from the current table. An index key based on the index expression is created in the index file for each record in the table. Visual FoxPro uses these keys to display and access records in the table.

    Note   Do not use a variable, an array element, or a field or field expression from a table in another work area for eExpression. If you access an index that contains a variable or field that no longer exists or cannot be located, Visual FoxPro generates an error message.

    Memo fields cannot be used alone in index file expressions; they must be combined with other character expressions. If you include a field prefaced by a table alias or work area letter in the index expression, Visual FoxPro generates an error message.

    Even though you can optimize FOR clauses with Rushmore Query Optimization technology if aliased fields are included, it is still highly recommended that you avoid using aliased fields when creating indexes. In several cases (USE ... AGAIN, SQL queries, and so on), a different alias is automatically assigned to a table and the index might not be properly updated or used. For more information on Rushmore technology, see Using Rushmore Query Optimization to Speed Data Access.

    If you attempt to build an index with a key that varies in length, the key will be padded with spaces. Variable-length index keys are not supported in Visual FoxPro.

    It is possible to create an index key with 0 length. For example, a 0-length index key is created when the index expression is a substring of an empty memo field. A 0-length index key generates an error message. When Visual FoxPro creates an index, it evaluates fields in the first record in the table. If a field is empty, it may be necessary to enter some temporary data in the field in the first record to prevent a 0-length index key.

    The length of an index key for an .idx index must be between 1 and 100 characters. The length of an index key for a .cdx index must be between 1 and 240 characters. Under some collating sequences, or when using Double-Byte Character Set characters, each character in eExpression uses two characters in the index key. In these cases, the length of eExpression is limited to 120 characters.

    Attempting to create an index with a key that exceeds the limit results in an "Invalid key length" error.

  • TO IDXFileName
    Creates an .idx index file. The index file is given the default extension .idx, which you can override by including a different extension or by changing the default index extension in the Visual FoxPro configuration file. Standard Windows rules for naming files, which include long file names, must be observed when creating index files.

  • TAG TagName [COLLATE cCollateSequence] [OF CDXFileName]
    Creates a compound index file. A compound index file is a single index file that consists of any number of separate tags (index entries). Each tag is identified by its unique tag name. Tag names must begin with a letter or an underscore and can consist of any combination of up to 10 letters, digits, or underscores. The number of tags in a compound index file is limited only by available memory and disk space.

    Multiple-entry compound index files are always compact. It is not necessary to include COMPACT when creating a compound index file. Names of compound index files are given a .cdx extension.

    The COLLATE cCollateSequence clause specifies a collation sequence other than the default setting, MACHINE. The cCollateSequence parameter must be a valid Visual FoxPro collation sequence. For more information about setting collation sequences, see Optimization of International Applications and SET COLLATE Command.

    Two types of compound index files can be created: structural and non-structural.

    If you exclude the optional OF CDXFileName clause from TAG TagName, you create a structural compound index file. A structural compound index file always has the same base name as the table and is automatically opened when the table is opened.

    If a table's structural compound index file cannot be located or is deleted or renamed, a dialog box appears when you try to open the table. If you choose the default Cancel push button, the table isn't opened. Choosing Ignore opens the table and removes the flag in the table's header that indicates an associated structural compound index file is present.

    Tip   To reassociate a structural compound index that has become dissociated from its table, issue the following command:

    USE TableName INDEX CDXFileName

    If you include the optional OF CDXFileName clause after TAG TagName, you create a non-structural compound index file. Unlike a structural compound index file, a non-structural compound index file must be explicitly opened with SET INDEX or the INDEX clause in USE.

    If a compound index file has already been created and opened, issuing INDEX with TAG TagName adds a tag to the compound index file.

    CDXFileName is the name of the dissociated structural compound index. Be sure to reindex the table if it has been modified since the structural compound index was dissociated.

  • FOR lExpression
    Specifies a condition whereby only records that satisfy the filter expression lExpression are available for display and access; index keys are created in the index file for just those records matching the filter expression.

    Rushmore optimizes an INDEX ... FOR lExpression command if lExpression is an optimizable expression. For best performance, use an optimizable expression in the FOR clause.

    For more information, see SET OPTIMIZE and Using Rushmore Query Optimization to Speed Data Access.

  • COMPACT
    Creates a compact .idx file.

  • ASCENDING
    Specifies an ascending order for the .cdx file. By default, .cdx tags are created in ascending order (you can include ASCENDING as a reminder of the index file's order). A table can be indexed in reverse order by including DESCENDING.

  • DESCENDING
    Specifies a descending order for the .cdx file. You can't include DESCENDING when creating .idx index files. You can, however, specify a descending order for an .idx index file with SET INDEX and SET ORDER.

  • UNIQUE
    Specifies that only the first record encountered with a particular index key value is included in an .idx file or a .cdx tag. UNIQUE can be used to prevent the display of or access to duplicate records. All records added with duplicate index keys are excluded from the index file. Using the UNIQUE option of INDEX is identical to executing SET UNIQUE ON before issuing INDEX or REINDEX.

    When a UNIQUE index or index tag is active and a duplicate record is changed in a manner that changes its index key, the index or index tag is updated. However, the next duplicate record with the original index key cannot be accessed or displayed until you reindex the file using REINDEX.

  • CANDIDATE
    Creates a candidate structural index tag. The CANDIDATE keyword can be included only when creating a structural index tag; otherwise Visual FoxPro generates an error message.

    A candidate index tag prevents duplicate values in the field or combination of fields specified in the index expression eExpression. The term "candidate" refers to the type of index; because candidate indexes prevent duplicate values, they qualify as a "candidate" to be a primary index.

    Visual FoxPro generates an error if you create a candidate index tag for a field or combination of fields that already contain duplicate values.

    For additional information about candidate and primary index tags, see Setting a Primary or Candidate Index.

  • ADDITIVE
    Keeps open any previously opened index files. If you omit the ADDITIVE clause when you create an index file or files for a table with INDEX, any previously opened index files (except the structural compound index) are closed.

Remarks

Records in a table that has an index file are displayed and accessed in the order specified by the index expression. The physical order of the records in the table isn't changed by an index file.

If SET TALK is ON, Visual FoxPro reports how many records are indexed during the indexing process. The record interval displayed during indexing can be specified with SET ODOMETER.

Use DISPLAY STATUS to display more information about open index files. This information includes the names of all open index files, their types (structural, .cdx, .idx), their index expressions, their collation sequences, and the name of the master index file or master tag.

The number of index files (.idx or .cdx) you can open is limited only by memory and system resources. In Visual FoxPro, FoxPro for Windows, and FoxPro for MS-DOS, the total number of files you can open is determined by the FILES setting in the MS-DOS Config.sys configuration file. For more information on the FILES setting, see your MS-DOS manual.

Index Types   Visual FoxPro makes it possible for you to create two types of index files:

  • Compound .cdx index files containing multiple index entries called tags.
  • .idx index files containing one index entry.

You can also create a structural compound index file, which is automatically opened with the table.

Tip   Because structural compound index files are automatically opened when the table is opened, they are the preferred index type.

Include COMPACT to create compact .idx index files. Compound index files are always compact.

Index Order and Updating   Only one index file (the master index file) or tag (the master tag) controls the order in which the table is displayed or accessed. Certain commands (SEEK, for example) use the master index file or tag to search for records. However, all open .idx and .cdx index files are updated as changes are made to the table. You can designate the master index file or tag with the INDEX clause of USE or with SET INDEX and SET ORDER.

Note   Replacing a key field changes the relative position in the index for the current record. Therefore you should be careful when performing an operation such as REPLACE ALL or SCAN ... ENDSCAN while changing the index for the scope. This also applies to REPLACE operations on indexes built with a FOR clause.

User-Defined Functions   Although an index expression can contain a user-defined function, you should not use user-defined functions in an index expression. User-defined functions in an index expression increase the time it takes to create or update the index. Also, index updates may not occur when a user-defined function is used for an index expression.

If you use a user-defined function in an index expression, Visual FoxPro must be able to locate the user-defined function. When Visual FoxPro creates an index, the index expression is saved in the index file, but only a reference to the user-defined function is included in the index expression.

Example

Example 1 opens the customer table and creates an index file named complist, which displays and processes records in the alphabetic order of the company field.

In Example 2, the customer table is again opened and an index file named citycomp is created from a substring of the first five characters of the city field and the first six characters of the company field. When this index file is used, records in the table are ordered primarily according to the city field and secondarily according to the company field.

In Example 3, index tags are created. The first tag is a structural compound index tag for address. The second tag is created in a non-structural index file named custcdx.

* Example 1
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
USE Customer     && Open customer table
INDEX ON company TO complist
CLEAR
DISPLAY STATUS

* Example 2
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
USE Customer     && Open customer table
INDEX ON SUBSTR(city,1,5) + SUBSTR(company,1,6) TO citycomp
CLEAR
DISPLAY STATUS

* Example 3
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
USE Customer     && Open customer table
INDEX ON address TAG address
INDEX ON company TAG company OF custcdx
CLEAR
DISPLAY STATUS

See Also

ALTER TABLE | CDX( ) | COPY INDEXES | COPY TAG | DELETE TAG | DESCENDING( ) | FOR( ) | INDEXSEEK( ) | KEY( ) | MDX( ) | NDX( ) | ORDER( ) | REINDEX | SET COLLATE | SET INDEX | SET ODOMETER | SET ORDER | SET TALK | SET UNIQUE | SORT | SYS(14) | SYS(21) | SYS(22) | SYS(2021) | TAG( ) | TAGCOUNT( ) | USE