Index Creation Based on Expressions

Article
10/23/2006

You can increase the power of your applications by creating indexes based on expressions. These expressions can be simple or complex, depending upon what you want to accomplish.

Indexing on Simple Expressions

Simple index expressions are indexes based on single fields, or on the concatenation of two or more character fields to form a multifield key. For example, you might want to create an index for the Customer table in the TasTrade database based on the expression:

country + region + cust_id

When you browse the Customer table sorted by this index tag, you see the customers ordered by country, then region, then customer ID.

Preventing Duplicates in a Combination of Fields

If you want to prevent duplicating values across multiple fields, you can create a primary or candidate index based on an expression combining multiple fields.

For example, you may have a table that stores the area code and phone number in two columns:

Area Code	Phone Number
206	444-nnnn
206	555-nnnn
313	444-nnnn

Both the area code field and the phone number field contain values that duplicate the values in other rows. However, no phone numbers are duplicated, because it is the combination of the two fields that makes up the value. Yet, if the primary or candidate index specified both columns in the index expression, the rows in the example would not be considered duplicates. If you attempted to enter a value that was exactly the same area code and phone number as one of the existing rows, Visual FoxPro would reject the entry as a duplicate.

Using Null Values in Index Expressions

You can create indexes on fields that contain null values. Index expressions that evaluate to .NULL. are inserted into the .cdx or .idx file before non-null entries. All null values are placed at the beginning of the index.

The following example demonstrates one effect of indexing null values. This is the state of the table before the index is applied:

Null values appear in the SocSec field for two records.

The value .NULL. in two records represents the fact that Social Security numbers for Anne Dunn and Alan Carter are either unknown or unavailable. You then create an index with the Social Security number using the following example:

INDEX ON SocSec + LastName + FirstName TAG MyIndex

When you view the table sorted by this index, you see the sort order as shown in the following figure.

After indexing on SocSec, records containing null SocSec values appear first.

When the index expression contains null values, the records whose SocSec values are .NULL. are sorted first (by LastName), followed by the records whose SocSec values are non-null. Notice that there are two entries for Alan Carter. Because record 5 contains a null value, record 5 is indexed before record 2.

Indexing on Complex Expressions

You can also create indexes based on more complex expressions. Visual FoxPro index key expressions can include Visual FoxPro functions, constants, or user-defined functions.

The expression you create must evaluate to no more than 100 characters for a stand-alone (.idx) index or 240 characters for a .cdx index tag. You can use fields of different data types together in a single tag by converting the individual components of the expression to character data.

To take advantage of Rushmore™ optimization, the index expression must exactly match the criteria.

Using Visual FoxPro Functions in an Index Tag

You can use Visual FoxPro functions in an index tag. For example, you can use the STR( ) function to convert a numeric value into a character string. If you wanted to create an index tag for the customer table that combined the cust_id field with the maxordamt field, you could convert the maxordamt field from a Currency field with a width of 8 to an 8-character field with 2 decimal places using the following code:

INDEX ON cust_id + STR(maxordamt, 8, 2) TAG custmaxord

If you want to reduce the size of indexes for fields with integer values, you can convert the integer values to a binary character representation using the BINTOC( ) function. You can also convert the binary values to integer values using the CTOBIN( ) function.

If you want to create an index to sort a table in chronological order, you can use the DTOS( ) function to convert a date field to a character string. To access the employee table by hire_date and emp_id, you can create this index key expression:

INDEX ON DTOS(hire_date) + emp_id TAG id_hired

Including Stored Procedures or User-Defined Functions

You can increase the power of your index by referencing a stored procedure or a user-defined function in your index expression. For example, you can use a stored procedure or a UDF to extract the street name from a single field that includes both the street number and street name. If the street number is always numeric, the stored procedure or UDF can return the character portion of the field and pad the field with spaces as needed to create a constant-length index key. You can then use this index key to access records in the table in street-name order.

You may prefer to use a stored procedure rather than a UDF in your index tag, if your table is associated with a database. Because a UDF is stored in a file that is separate from the database, it is possible to move or delete the UDF file, which then causes the index tag referencing the UDF to become invalid. In contrast, stored procedure code is stored in the .dbc file and can always be located by Visual FoxPro.

Another benefit to using a stored procedure in an index tag is that referencing a stored procedure guarantees that the index is based on the exact code you specify. If you use a UDF in your index expression, any UDF that is in scope at the time of indexing and has the same name as the UDF referenced in your index will be used.

Note Exercise care when referencing a stored procedure or UDF in an index expression, as it increases the time required to create or update the index.

Using Data in a Field in Another Table

You can create an index tag that refers to a table open in another work area. It's wise to use a stand-alone index (.idx) for any tag that refers to more than one table. This is because if you were to include a tag referring to another table in a structural .cdx file, Visual FoxPro wouldn't allow you to open the table until you opened the table referenced in the index tag.