INDEX

Topic group Related topics Example

Creates an index for the current table.

Syntax

For DBF tables:

INDEX ON <key exp>
TAG <tag name>
[OF <.mdx filename>]
[FOR <condition>]
[DESCENDING]
[UNIQUE | DISTINCT | PRIMARY ]

or to create dBASE III-compatible .NDX index files:

INDEX ON <key exp> TO <.ndx filename> [UNIQUE]

For DB and SQL tables:

INDEX ON <field list>
PRIMARY | TAG <tag name> [UNIQUE]

For DBF tables, <key exp> can be a dBL expression of up to 220 characters that includes field names, operators, or functions. The maximum length of the key—the result of the evaluated index <key exp>—is 100 characters.

For Paradox and SQL tables, indexes can't include expressions; however, you can create indexes based on one or more fields. In that case, you specify the index key as a <field list>, separating the name of each field with a comma.

TAG <tag name>

Specifies the name of the index tag for the index

OF <.mdx filename>

Specifies the .MDX multiple index file that dBASE Plus adds new index tags to. If you do not specify an .MDX file, index tags are added to the production .MDX file. If you specify a file that doesn't exist, dBASE Plus creates it and adds the index tag name. By default, dBASE Plus assigns an .MDX extension and saves the file in the current directory.

TO <.ndx filename>

Specifies the name of an .NDX index file.

FOR <condition>

Restricts the records dBASE Plus includes in the index to those meeting the specified <condition>.

DESCENDING

Creates the index in descending order (Z to A, 9 to 1, later dates to earlier dates). Without DESCENDING, INDEX creates an index in ascending order.

UNIQUE

For DBF tables, prevents multiple records with the same <key exp> value from being included in the index; dBASE Plus includes in the index only the first record with that value. For DB and SQL tables, specifies creating a distinct index which prevents entry of duplicate index keys in a table.

DISTINCT

Prevents multiple records with the same <key exp> value from being included in the table; any such attempt causes a key violation error. Records marked as deleted are never included in a DISTINCT index. DISTINCT indexes may be created for DBF tables only.

PRIMARY

Specifies that the index is the primary key for the table. For DBF tables, the PRIMARY index is a distinct index that is designated as the primary index; it currently has no other special meaning. For DB and SQL tables, the primary key has a specific meaning. A table may have only one primary key.

Description

Use INDEX to organize data for rapid retrieval and ordered display. INDEX doesn't actually change the order of the records in a table but rather creates an index in which records are arranged in numeric, alphabetical, or date order based on the value of a key expression. Like the index of a book, with ordered entries and corresponding page numbers, an index file contains ordered key expressions with corresponding record numbers. When the table is used with an index, the contents of the table appear in the order specified by the index.

DBF expression indexes

To index on multiple fields in a DBF table, you must create an expression index. When combining fields with different data types, use conversion functions to convert all the fields to the same data type. Most multi-field expression indexes are character type; numeric and date fields are converted to strings using the STR( ) and DTOS( ) functions. When using the STR( ) function, be sure to specify the length of the resulting string so that it matches the numeric field.

Note

Do not use the DTOC( ) function to convert a date to a string. In many date formats, the day comes before the month, or the month comes before the day and year, resulting in records in the wrong order.

To concatenate the fields, use the + or - operators.

Warning!

Do not create an index where the length of the index key expression varies from record to record. Specifically, do not use TRIM( ) or LTRIM( ) to remove blanks from strings unless you compensate by adding enough spaces to make sure the index key values are all the same length. The - operator concatenates strings while rearranging trailing blanks. Varied key lengths may cause corrupted indexes.

If a function is used in a key expression, keep in mind that the index is ordered according to the function output. Thus, when you use search for a particular key, you must search for the key expression as it was generated. For example, INDEX ON SOUNDEX(Name) TO Names creates an index ordered by the values SOUNDEX( ) returns. When attempting to find data by the key value, you would have to use something like SEEK SOUNDEX("Jones") rather than SEEK "Jones".

FOR <condition> limits the records that are included in the index to those meeting the specified condition. For example, if you use INDEX ON Lastname + Firstname TO Salaried FOR Salary > 24000, dBASE Plus includes only records of employees with salaries higher than $24,000 in the index. The FOR condition can't include calculated fields.

The following built-in functions may be used in the index <key exp> and FOR <condition> expressions of a DBF index tag.

Index sort order

In an index, records are usually arranged in ascending order, with lowest key values at the beginning of the index. Using the DOS Code Page 437 (U.S.) character set, character keys are ordered in ASCII order (from A to Z and then from a to z); numeric keys are ordered from lowest to highest numbers; and date keys are ordered from earliest to latest date (a blank date is higher than all other dates). Use the UPPER( ) function on the key expression to convert all lowercase letters to uppercase and achieve alphabetical order for character-type indexes.

Note

Most non-U.S. character sets provide a different sort order for characters than the DOS Code Page 437 character set.

You can reverse the order of an index, arranging records in descending order, by including the DESCENDING keyword. (You can use DESCENDING only when building .MDX tags.)

Distinct, primary, and unique indexes

You may use an index to ensure that there are no duplicate key values. For example, in a table of customers, each customer is assigned their own unique customer ID number. To prevent an existing customer ID number from being used by another customer, you can create a special kind of index on the customer ID field. For DB and SQL tables, this type of index is called a unique index; the key value for each record in the table must be unique. For DBF tables, this type of index is called a distinct index; a unique index for a DBF table has a different meaning. For clarity, the DBF terms are used.

A distinct index is created with the DISTINCT option for DBF tables, and the UNIQUE option for DB and SQL tables. When a table has a distinct index, any attempt to create a duplicate key entry, either by adding a new record with a duplicate value or by changing an existing record so that its key field(s) duplicates another record, causes a key violation error. The new or changed record is not written to the table. Distinct indexes for DBF tables never include records that are marked as deleted.

A table may also have one distinct index designated as its primary index, or primary key. A primary index is usually created for the ID field or fields that uniquely identify each record in the table. For example, while you may index on the customer’s name, their ID field is what uniquely identifies each customer, and that is the field you use for the primary key. For DB tables, a table’s primary key determines the default order for the records in the table, and you must have a primary key to create other secondary indexes. For DBF tables, a primary key currently has no special meaning, other than self-documenting the primary key field(s) of the table. The PRIMARY clause is used to create the primary index. For DB and SQL tables, a primary index may have no other options other than the field list.

DBF tables support a kind of index that allows duplicate key values in the table, but only shows the first such record in the index. These are called unique indexes, not to be confused with the distinct unique indexes used by DB and SQL tables. For example, you may be interested in the names of the cities in which your customers reside. By using a unique index, each city is listed once (alphabetically), no matter how many customers you have in that city.

A record’s index key value is tested for uniqueness only when the record is added or updated. For example, suppose you have a unique index on the City field, and have records in both "Bismark" and "Fargo". If you append another record in "Bismark", it does not appear in the index, although the table is updated with the new record. If you then change the first record, which was listed in the index, from "Bismark" to "Fargo", then it too becomes hidden because there is already a "Fargo" in the index. It also does not automatically expose the other record with "Bismark", because that record was not updated; no records in "Bismark" are in the index at that moment. REINDEX explicitly updates all key values in a unique index.

Indexing a table with SET UNIQUE ON has the same effect as INDEX with the UNIQUE option. With DB and SQL tables, it creates a distinct index. With DBF tables, it creates a unique index.

Using indexes

Once a table has been indexed, use LOOKUP( ), SEEK, and SEEK( ) to retrieve data. The structure of an index file allows these commands to quickly locate values of the key expression.

Whenever data in key fields is modified, dBASE Plus automatically updates all open index files. Index files closed when changes are made in a table can be opened and then updated using REINDEX.

Multiple index files simplify updating indexes, since dBASE Plus updates all indexes with tag names listed in .MDX files specified with USE...ORDER or SET ORDER. dBASE Plus automatically opens a production .MDX file, if one exists, when you open the associated table.

INDEX...TAG creates an index and adds the tag name to a multiple index file. If you don't include OF <filename>, INDEX...TAG adds the tag name to the production .MDX file. dBASE Plus creates the production .MDX or the specified file if it doesn't already exist.

INDEX is similar to SORT, another command that allows ordering of a table. Unlike INDEX, though, SORT physically rearranges the table records, a time-consuming process for large files. To maintain the sorted order, either new records must be inserted in their proper position, which is also very time-consuming, or the entire table must be resorted. Also, SORT doesn't support LOOKUP( ), SEEK, or SEEK( ), making the process of locating data in a sorted table much slower.

At the end of an indexing operation, the new index file is the master index, and the record pointer is positioned at the first record of the new indexed.

OODML

Use the Database object’s createIndex( ) method.