Creates an index file to display and access table records in a logical order.
Syntax
INDEX ON eExpression TO IDXFileName | TAG TagName [OF CDXFileName]
[FOR lExpression]
[COMPACT]
[ASCENDING | DESCENDING]
[UNIQUE | CANDIDATE]
[ADDITIVE]
Arguments
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 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 "Understanding Rushmore Technology" in Chapter 15, Optimizing Applications, in the Programmer's Guide.
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 aren't 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.
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 [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 isn't necessary to include COMPACT when creating a compound index file. Names of compound index files are given a .cdx extension.
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 "Understanding Rushmore Technology" in Chapter 15, Optimizing Applications, in the Programmer's Guide.
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" in Chapter 7, Working with Tables, in the Programmer's Guide.
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 lets you create two types of index files:
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.
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.