Indexes are additional storage objects for a database table that enable fast searching and sorting. TurboDB indexes are built on either a list of field names or an expression to define the sorting order. If an index is declared to be unique, records that would create a duplicate key in the index are not accepted. Another kind of indexes are full-text indexes.

Indexes Based on a Field List

These indexes are sorted in the order of the first field in the field list. If two records have the same value for the first field they are sorted after the second field of the field list and so on. There can be up to 10 fields in the index field list. Every field can be sorted in ascending or in descending order.

Indexes Based on an Expression

These indexes are sorted after the value of an arbitrary expression that can be up to 40 characters long. If the expression is of string type, the index is sorted like if the expression values were values of a string column. If the expression is of numeric type, the index is sorted according to normal numeric order.

Full-Text Indexes

A full-text index enables the user to search for a keyword or a set of keywords in any field of the table. Full-text indexes require a separate table, the dictionary table, which contains the indexed words. Full-text indexes are implemented differently for table level 4 and the levels below. In table level 4, there is only one storage object to make-up the connection between the dictionary and the table, it has the extension fti or tdbf. In the older table levels, the connection was implemented using relation fields, which requires an additional base table (extension rel) and two indexes (extension in1 and in2).

System Indexes

Using data types AutoInc, Link and Relation will automatically generate one ore more indexes. Depending on the table level these (system) indexes are named as the table or start with prefix 'sys_'. Modifying or deleting of these indexes is not possible.

 

Indexes can be created and deleted with various TurboDB tools at design time. At run-time, you can use TurboSQL to create, update and delete indexes. Also some component libraries (e.g. VCL) contain methods for adding and deleting indexes.