Skip to content

Creating Indexes

While Database Builder can be used to maintain tables, we recommend that you use the Studio to maintain tables during development.

The Index tab page of the File Definition dialog displays the table’s indexes (sort orders).

You must open a table in exclusive mode before you can make any changes to the indexes. See "Opening a Table" and "Switching Open Modes" for more information.

This tab page is divided into two panels:

  1. Indexes Overview (left panel): This is a treeview structure that displays all indexes currently defined for the table.
  2. Index Details (right panel): This panel displays details of the currently selected index in the overview panel.

A splitter between these two panels allows you to adjust the relative widths of each panel.

Indexes Overview

This treeview displays all the indexes from the current table. Below is a guide to the icon symbols used to represent each index.

Index Node This node is used to indicate an index. To the right of this symbol is the index number. If you are editing an SQL table, then the index name may also be displayed. The list of index segment columns are listed as child nodes. You can expand or collapse the index details.

Index Segment Node This node is used to indicate an index segment column. Each index is composed of one or more of these columns.

Index Details

To view the details of an index, first select it in the Indexes Overview. The Index Details panel will display the following information:

Index Number

The index number is used in your DataFlex source code to refer to the index you want to use when you are finding or sorting rows.

You can change the index's number by typing the new index number and then tabbing out of this field. You must enter an unused index number. After changing the index number, you will see the index definition move to the appropriate position in the Indexes Overview treeview.

Warning: Changing an index's number may affect your applications' ability to find or sort rows from this table. If your application uses hard-coded index numbers, then these references may need to be adjusted.

Key Length / Levels

The aggregate length of the index (Key Length) and the number of index levels needed by the ISAM process (Index Levels) are calculated as you enter and edit segments for an index. You cannot edit these parts of the display—they are there for your reference only.

Generally, the larger the number of index levels needed by an index, the slower the performance of add, delete, and edit operations on the table.

For embedded database tables, the total length of all the segments used in an index cannot exceed 256 bytes. Other database types may support different parameters.

Batch Index

By default, each index you create will be maintained online. Online indexes are updated every time a change is made to any of the columns that make up the index in any row in the table.

The Embedded database driver supports the concept of Batch indexes. Batch indexes are not updated during data entry. They are updated only when specifically processed with Reindex. Batch indexes are for situations where an index does not have to be constantly up-to-date, for example, when it is used only for occasional or periodic reports.

If you wish a newly created index to have batch status, you must set it to that status by checking the Batch box.

If you wish to change a Batch index to online, Database Builder automatically presents the option of sorting the data to ensure that the index is current.

Primary Index

Some database drivers support the specification of a Primary Index (the Embedded driver does not). Primary indexes are used when performing key column searches.

Check the "Index is the Primary Index" box to make the current index the primary index.

Other Index Settings

If you are editing an SQL database, then there may be other index settings displayed in this panel. Refer to your database driver user's guide for more information.

Index Segment Columns

This is a list of index segment columns. These columns define the sort order for the currently highlighted index.

The ordering of the columns is important. The data is sorted by the first column, then the second column, third column, etc. For example, all rows with the same value in the first index segment column will then be sorted by the value of the second index segment column, then third, etc.

The following operations can be performed in this list:

  • You can change the column name for the currently highlighted segment. To do this, you can drop down the combo list and select the table column you would like to use in this segment. Alternatively, you can simply enter the column name. As you type the column name, Database Builder will select the first column that matches the name you have typed so far.
  • You can add a new segment to the index. To add a new segment, click the 'Add Segment' button on the toolbar or simply go to the bottom row of the list and begin selecting columns.
  • You can remove a segment from the index. To remove a segment, highlight the segment in the list and click the 'Delete Segment' button on the toolbar.
  • You can adjust the position of a segment in the list. To move a segment up or down, highlight the segment that you want to move, then click the 'Promote Segment' or 'Demote Segment' toolbar buttons. Note: Changing the position of an index segment affects the ordering of rows for that index.

A given column can be used in more than one index, but for each column, there can be only one Main Index. See the discussion under "Main Index".

Ignore Case

This option can be checked for ASCII index segment columns only. If you check "Ignore Case," all letters ('a'-'z') in column values will be treated in the index as being capital letters.

For example, if one column contains the data "Smith, Robert," it will be the exact equivalent (for indexing purposes only) of a column containing "SMITH, ROBERT." This eliminates the sort of confusion users experience when capitalization of letters in the data affects the order in which rows are listed or found.

Descending

If you check "Descending," the segment will rank from high to low, rather than the default low-to-high. For example, the ASCII value "Pears" would rank before "Apples."

This is useful, for example, where you want to list customers in a customer table with the largest sales value first. Here you would check descending for the Sales_Value segment of the index.

Index Toolbar

The Index tab contains a toolbar that allows you to perform common index maintenance operations.

Add Index

Click this button to create a new index for the table. Embedded database tables may define up to 16 indexes. Other database types may support more indexes.

Delete Index

Click this button to delete the currently highlighted index in the Indexes Overview panel.

Add Segment

Click this button to add a new segment to the currently highlighted index's segment list. Embedded database tables support up to 16 segments per index. Other database types may support more or fewer segments.

Delete Segment

Click this button to delete the currently highlighted segment from the segment list of the current index.

Promote Segment

Click this button to move the currently highlighted segment up in the index's segment list.

Demote Segment

Click this button to move the currently highlighted segment down in the index's segment list.

Unique Indexes

Each time a row is saved in a table, DataFlex updates the online indexes with information from the new row. If any of the index values matches that of any existing row in the table, then the save will not be permitted. For example, if you have a Customer table and Index #1 for this table is the Customer_Number column, then DataFlex will not allow two rows to be saved with the same customer number.

In the case of the customer number, this is what you would want, but what if you also have Index #2 that sorts the data by Customer_City? Does this mean that you cannot store two rows with the same city? The answer is you must add another segment to the Customer_City index so that the combined segments are unique to their row. For example, you could add the Customer_Number column to the Customer_City index. This guarantees that Index #2 is unique for each row regardless of matching city names, because Customer_Number is always unique.