Index Creation Based on Expressions

You can increase application performance by creating indexes based on expressions. Expressions can range from the simple to the complex depending upon the tasks you want to accomplish. You can perform different tasks by creating indexes with expressions as described in the following sections:

• Indexes Based on Simple Expressions

• Indexes Based on Complex Expressions

• Fields with Null Values in Index Expressions

You can also increase the speed for queries, reports, or other operations using index expressions that use Rushmore optimization. For more information, see Optimizing Applications.

Indexes Based on Simple Expressions

You can create indexes using simple index expressions, which are based on a single field or the concatenation of two or more character fields. You can also create index expressions containing fields from another table.

Tip

If you want to include fields with different data types in an index expression, convert any non-character fields to character fields by enclosing the field with the STR( ) function.

For example, suppose you want to organize records by a maximum order amount and then by company name. The following code converts the MaxOrdAmt field, which has type Currency, so it can be concatenated with the Company field, which has type Character:

STR(customer.maxordamt,20,4) + customer.company

For more information, see STR( ) Function.

One or More Fields in Index Expressions

When you include one or more fields in an index expression, you can perform the following tasks:

• Sort data in the order that the fields appear in the index expression.

  Visual FoxPro evaluates fields in the order that they appear in the expression. For example, suppose you want to view records in a customer table sorted by country, region, then customer ID. The following expression creates an index for the customer table by using a plus sign (+) to include multiple fields:

  customer.country + customer.region + customer.cust_id
  

• Prevent duplicate values in tables by using a primary or candidate index that includes one or more fields.

  For more information about controlling duplicate values, see Duplicate Values in Fields.

• Optimize performance and increase speed for queries and views with filters on more than one field by including more than one field in the index expression.

  By including fields that change the least at the beginning of the expression, indexing can be limited to those fields whose values change more frequently. For example, suppose you want to organize records in a customer table by country, postal code within the country, and the company name within the postal code. In the following example, the Country field appears in the beginning of the list because it changes the least:

  customer.country + customer.postal_code + customer.company
  

Fields from Another Table in Index Expressions

You can create an index expression that refers to fields in another table, for example, one that is open in another work area. However, it is recommended that you store any index tag that refers to more than one table in a standalone index (.idx) file instead of the associated structural compound index (.cdx) file. When an index tag for one table refers to another table, Visual FoxPro does not permit opening the table associated with the .cdx file until you open the other table that the index tag references.

Indexes Based on Complex Expressions

You can create indexes using complex expressions, which can contain constants, Visual FoxPro functions, stored procedures, or user-defined functions. To take advantage of Rushmore Query Optimization, the index expression must exactly match the criteria.

Visual FoxPro Functions in Index Expressions

You can use Visual FoxPro functions in an index expression.

For example, you can use the STR( ) function to convert a numeric value into a character string. Suppose you wanted to create an index for a customer table that combines the customer ID field with the maximum order amount field. You can convert the MaxOrdAmt field from a Currency field to a Character field 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. For example, suppose you wanted to access an employee table by the hire date and employee ID. The following example code creates the index expression that performs this task:

INDEX ON DTOS(hire_date) + emp_id TAG id_hired

For more information, see STR( ) Function, BINTOC( ) Function, CTOBIN( ) Function, and DTOS( ) Function.

Stored Procedures or User-Defined Functions in Index Expressions

You can increase the power of an index by referencing a stored procedure or including a user-defined function (UDF) in the index expression.

Note

Certain considerations exist when you include a UDF in an index expression. Referencing a stored procedure or using a UDF in an index expression increases the time required to create or update the index. For more information, see Considerations for Creating Index Expressions.

For example, you can use a stored procedure 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 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.

Fields with Null Values in Index Expressions

You can create indexes on fields that contain null values. Visual FoxPro inserts index expressions that evaluate to null (.NULL.) into the compound index (.cdx) or standalone index (.idx) file preceding index expressions that do not evaluate to null. Therefore, all null values appear at the beginning of the index.

For example, the following diagrams demonstrate one effect of indexing null values. Before an index is applied, the first diagram shows the Social Security field for the third and fifth records contain null (.NULL.) values, which indicate that Social Security numbers are either unknown or unavailable.

Null values appear in the SocSec field for two records.

The following example code creates an index using an index expression containing the Social Security field:

INDEX ON SocSec + LastName + FirstName TAG MyIndex

When the index containing the Social Security field is applied, the table first displays those records that contain null values for Social Security numbers, sorted by last name in descending order, followed by the remaining records ordered by Social Security number.

Note

Two entries exist for Alan Carter. However, because record number 5 contains a null value, record number 5 is indexed before record number 2.

Records that contain null SocSec values appear first.

See Also

Tasks

How to: Create Indexes (Visual FoxPro)

Reference

Considerations for Creating Index Expressions

Other Resources

Working with Table Indexes