Creates an index file to display and access table records in a logical order.
INDEX ON eExpression TO IDXFileName | TAG TagName [OF CDXFileName] [FOR lExpression] [COMPACT] [ASCENDING | DESCENDING] [UNIQUE | CANDIDATE] [ADDITIVE]
- 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 Using Rushmore 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 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.
- Creates a compact .idx file.
- 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.
- 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.
- 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.
- 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.
- 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.
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 1 opens the
customer table and creates an index file named
complist, which displays and processes records in the alphabetic order of the
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
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
* 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
ALTER TABLE | CDX( ) | COPY INDEXES | COPY TAG | DELETE TAG | 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