FlexODBC uses the ORDER BY clause in SQL and attempts to match the first fields (columns) of the ORDER BY to an existing index. If a match is found, that index is used for the sort order. If no match is found, the data is retrieved by the driver and sorted with a sort engine in memory.

If the ORDER BY clause and the WHERE clause use the same field names, the system will "jump-in" to the index to find records, and "jump-out" when the set is complete, increasing performance.

Here is the format of a command to recreate indexes:

sp_reindex ALL|tablename {as “path”} {ALL}|{Index#}|{#}

This command can be entered in our VBDEMO program, or your SQL command editor of choice. See the Security section for details on this. For example, you can re-create all of the indexes in your system using the following command:

sp_reindex ALL

You can also pass in parameters stating the single file to reindex as shown:

sp_reindex CustData

You can create a new index on an existing file with the following command:

CREATE UNIQUE INDEX 5 ON ANTIQUEOWNERS (OWNERLASTNAME,OWNERFIRSTNAME)

All indexes in DataFlex format tables are unique indexes. Add the RECORD_NUMBER field to the end of an index to allow the primary index fields to contain non-unique data.

Delete an index as follows:

DROP INDEX 5

Note: you will not be allowed to modify index files that you do not have security permissions for, see How do I use FlexODBC Security?  for details on this.