Skip to content

SQL Index Types

In SQL, the “where” clause determines what rows are found (where “found” means a row is considered valid and can be sent from the server to your DataFlex application). The SQL “order by” clause determines the order that the found rows are presented. These SQL clauses do not determine the strategy used for finding and ordering these rows – that is up to the SQL Server engine. The simplest strategy for the SQL server is to read all rows, filter out the invalid rows, and sort the result. While simple, this approach is neither efficient nor scalable when dealing with large tables. This is why indexes are used in SQL databases.

An SQL Index makes it possible to find and order rows more efficiently. Rather than reading an entire table, you can jump into the index, find the rows, jump out of the index, and return the row with little or no sorting. Indexes make row finding both efficient and scalable. The SQL engine will determine if and how an index will be used by looking at the SQL statement’s where and order by clause. If a complete index can be used, it will use that. If a partial index can be used, it will use that and sort the results. If no index can be used, it will read all rows, filter them, and sort the results.

In DataFlex, your table definition (.INT file) declares a list of unique indexes consisting of an index number and its definition (either an SQL index name or a list of index segment definitions). Typically, you will also create an SQL Index that exactly matches the index definition. The DataFlex and SQL index will be the same and will be determined by the SQL table’s index definition. When the DataFlex definition of the index is changed in Table Editor, it changes the SQL index. This is called a server index.

You may also define DataFlex indexes that only exist on the DataFlex side (i.e., defined in the INT file but not defined by the back end). When the definition of the index is changed by Table Editor, no changes are made on the SQL side. How does this work? When DataFlex Finds occur, the defined index will be used to build an SQL statement with a where and order by clause that is created as if the index existed on the back end. The SQL server will receive this statement and generate a finding strategy that finds the rows as effectively as it can. If appropriate indexes are available on the server, the SQL engine will use them and filter and sort as needed. These appropriate indexes can be partial (non-unique). This is called a client-only index.

From the DataFlex point of view, server and client-only indexes are the same. They are both represented by an index number. They are both defined to be unique. They filter and sort rows in the same order. The only difference will be server overhead, performance, and scalability.

SQL tables may have indexes that DataFlex cannot use directly. If an SQL index is not unique, the DataFlex style of record finding will not work reliably. You should not try to use these types of indexes in your application. These are referred to as server-only indexes.

Often, SQL indexes are non-unique and are designed to only cover part of the finding order. For example, an SQL index may consist of just one non-unique segment, Customer.Name. Because this is not unique, it is a server-only index and should not be used directly in DataFlex. However, you could define a client-only index that makes this unique (Customer.Name x Customer.Customer_Number). When this index is used, the SQL engine will use the partial SQL index, and you should get very good results. This makes it possible to connect to existing SQL Tables and to use their existing table indexes without needing to change the SQL table definition. When the SQL Connect/Repair Wizard is used to connect to an existing table, the wizard will take server-only indexes and create client-side DataFlex indexes by completing them. The wizard does this by appending the primary key to its client-only definition. No changes are made to the actual SQL Table.

To summarize: DataFlex supports three types of indexes. These are defined using Table Editor and are controlled using the DF_INDEX_SQL_TYPE attribute.

  • Server: Exists on server, defined in the DataFlex .INT file (mapping an index name to an index number). Modification will require restructure. This is your standard DataFlex style index.

  • Client Only: Defined only in the DataFlex .INT file. Modification will require .INT file update.

  • Server Only: Exists on server, not defined in DataFlex .INT file. Modification will change to its server and require restructure. These indexes should not be referenced in source code, as their numbers can dynamically change.

See Also