Considerations for Creating Index Expressions
When creating index expressions, be aware of the following considerations:
If you specify an expression that exceeds the limits set for creating index keys, Visual FoxPro generates an error.
The length of an index key in a compound index (.cdx) file must be between 1 and 240 characters. The length of an index key in an index (.idx) file must be between 1 and 100 characters. With some collating sequences, or when using double-byte character set (DBCS) characters, each character in the index expression uses two characters in the index key. In these cases, the length of the index expression is limited to 120 characters.
If you create an index expression using numeric fields, the index organizes records by the sum of the fields, not the fields themselves.
Visual FoxPro does not support variable length index keys. If you attempt to build an index with a key that varies in length, the key is padded with spaces.
Do not use a variable, an array element, or a field or field expression from a table in another work area for the index expression.
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.
Even though Visual FoxPro generates an error, it is possible to create an index key with zero (0) length.
When Visual FoxPro creates an index, it evaluates fields in the first record in the table. Therefore, if the index expression is a substring of an empty memo field, a zero-length index key is created.
Tip
To prevent creating a zero-length index key if a field is empty, it might be necessary to enter some temporary data in the field of the first record.
Visual FoxPro pads index keys based on Character, Varchar, Varchar (Binary), and Varbinary fields and expressions.
Index keys based on Varchar, Varchar (Binary), and Varbinary fields and expressions include padding even though these data types do not usually include padding. Index keys based on Varchar and Varchar (Binary) are padded with additional spaces, similar to index keys based on Character fields and expressions. Index keys based on Varbinary fields or expressions are padded with zeros from the right side of the value in the field to the maximum length of the field. For example, the values 0hAA, 0hAA00, and 0hAA000 have the same index key of 0hAA0000. For more information, see Varchar Field Type and Varbinary Data Type.
Memo and Blob fields must be combined with other character expressions in index file expressions; they cannot be used alone. General fields cannot be used in index 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.
Avoid using aliased fields when creating indexes even though you can optimize FOR clauses with Rushmore Query Optimization technology when aliased fields are included.
In several cases, for example, with the USE...AGAIN command and SQL queries, 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 Query Optimization to Speed Data Access.
Although an index expression can contain a user-defined function (UDF), it is not recommended that you include a UDF in an index expression.
Though both stored procedures and user-defined functions in an index expression increase the time it takes to create or update the index, if your table is associated with a database, you might prefer to use a stored procedure rather than a UDF in your index expression.
If you use a UDF 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; however, only a reference to the UDF is included in the index expression. The UDF is stored in a file that is separate from the database; therefore, it is possible to move or delete the UDF file, which invalidates the reference in the index expression to the UDF.
In contrast, stored procedure code is stored in the table (.dbc) file and can always be located by Visual FoxPro. Also, referencing a stored procedure in an index expression guarantees that the index is based on the exact code you specify.
Also, index updates might not occur when user-defined functions are not in the scope of applications that modify the index. If you use a UDF in your index expression, Visual FoxPro uses any UDF that is in scope when indexing the table and that has the same name as the UDF referenced in the index.
If you use a UDF associated with a database in an index expression, Visual FoxPro handles the expression in the same way it handles rule and trigger expressions that contain UDFs. For more information, see Enforcing Business Rules and Trigger Usage.
Be sure to use functions invariant from the current code page setting when creating an index that should be modified under different current code pages. Results of some functions used in index expressions can vary depending on the current codepage.
For example, the UPPER( ) function uses the current code page to transform a string and might yield unexpected results if the index is modified under different current code pages or when the index is applied to a field and the current code page is not the same as the code page tagged to the table. You can use the STRCONV( ) function instead, STRCONV(...,8,...).
Visual FoxPro evaluates index expressions by reading character data from the table without additional code page translation even when current code page (CPCURRENT()) does not match code page tagged to the table (CPDBF()) in order to produce the same index regardless of the current code page.
See Also
Tasks
How to: Create Indexes (Visual FoxPro)