Skip to content

DF_INDEX_CLUSTERED

See Also: Get_Attribute, Set_Attribute

Allows you to define or query a clustered index in a SQL database (MSSQL, DB2, or any database connected through ODBC that supports clustered indexes).

Level

Index

Supported by

The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 6 and higher.

Type

Numeric, permanent

Access

Read / Write

Values

  • True
  • False

Syntax

Use cli.pkg
Get_Attribute DF_INDEX_CLUSTERED of {tableNumber} {indexNumber} to {BooleanVariable}
Set_Attribute DF_INDEX_CLUSTERED of {tableNumber} {indexNumber} to {True|False}

Remarks

A clustered index is a SQL database concept that is supported by most SQL databases. A table can have only one clustered index. Usually, the primary key index (DF_INDEX_SQL_PRIMARY_KEY) is defined as a clustered index. Setting DF_INDEX_SQL_PRIMARY_KEY to true will also set DF_INDEX_CLUSTERED to true.

To create a table with a clustered Primary Key in the Studio, you would:

  1. Create a New Table (e.g., Customer).
  2. Create a column (or columns) for your PK (e.g., Customer.Number).
  3. Create an Index for your PK (e.g., Create one segment Customer.Number Index).
  4. For that index, set the attribute DF_INDEX_SQL_PRIMARY_KEY to True.

This will define the PK and will automatically make this a clustered index. This technique will work with both standard and legacy style (Recnum) tables.

A clustered index is used to improve performance and is usually assigned to the PK index. There may be rare cases where you would not want your PK index to be clustered (if you want either no clustered index or a different clustered index). In such a case, you can use the DF_INDEX_CLUSTERED attribute to assign a different or no clustered index. You can only have one PK and one clustered index. Therefore, DF_INDEX_SQL_PRIMARY_KEY and DF_INDEX_CLUSTERED can only be set to True for one or no indexes.

When set to True inside a restructure, the index will be created as a clustered index.

This attribute can only be set inside a Structure_Start ... Structure_End operation.

Since a table can have only one clustered index, if there was some other index defined as clustered, for this other index, DF_INDEX_CLUSTERED will be turned off.

If you are using Recnum tables, your Recnum column cannot be designated as the PK or the clustered index. This should not be an issue, as it is not recommended to use Recnum as your only unique column (i.e., you don't use Recnum in relationships).