SQL Indexes
Conceptually, indexes work the same way with embedded and SQL tables. Indexes are used to find rows quickly and in a consistent order. They allow you to find a row via an exact match (EQ) and to find the next (GT) or previous (LT) row using a specified order.
With SQL tables, the number of indexes per table and the segments per index are limited by the SQL database. They are not limited to 15 like the embedded database. SQL indexes are identified by name and must be mapped to an index number within the table INT file. Because you may have more than 15 indexes per table, it is suggested that you refer to the index number by its number and not its symbolic number constant (e.g., use 1 instead of index.1) as the symbols are only defined up to index.15 (e.g., you cannot refer to an index.16, but using 16 would be allowed).
SQL indexes in DataFlex are best understood by distinguishing between an index definition and a physical back-end index. With the embedded database, the definition of the index is the same as the physical index. In other words, defining the index creates a physical index using that definition. DataFlex requires that its index definitions be unique. Because the index definition must be unique, an embedded table’s physical index must always be unique.
This works a little differently with SQL indexes. The DataFlex definition of an index, which still must be unique, does not have to be an exact match with a physical SQL index. The SQL index may be partial (i.e., it may not cover all of the segments of the DataFlex index). A physical index does not have to exist at all. This is discussed in SQL Index Types.
Batch indexes are not supported with SQL tables. In their place, an even more powerful feature, runtime temporary indexes, allows you to use the power of the SQL server to filter and order your data any way you want. This is discussed in Temporary Indexes.
Each segment in an index can be defined as ascending or descending.
While text segments can be defined to be case-insensitive (uppercased), this technique is not advised when using SQL tables.
SQL indexes are declared, created, and modified in the Table Editor in the Studio.