Skip to content

Create_Index

See Also: Defining Indexes in the Studio, DF_INDEX_NUMBER_SEGMENTS, Set_Attribute, Structure_Start, DF_FILE_PHYSICAL_NAME for examples of creating a new table, Delete_Index

Purpose

To create an index for a database table.

Syntax

Create_Index {table-handle} [at {index-num}]

Where {table-handle} is a handle to the database table where the new index should be added; and {index-num} is the number assigned to the new index. {index-num} should be passed as a variable, since the number of the column created will be returned here.

What It Does

This command will create a new index for table {table-handle}. Create_Index must be used in a Structure_Start…Structure_End block for permanent indexes (see temporary indexes below). When the Structure_End executes, it will create the new index and populate it with data.

After the execution of the Create_Index command, {index-num} will contain the number of the index created. You cannot specify the number of the new index—it will be appended to the existing index structure. For example, if {table-handle} has 4 indexes, executing Create_Index will create index 5.

After creating an index, you can use the Set_Attribute command to set up the columns and other characteristics of the index.

For SQL databases, when adding a new index and there are Server_Only indexes present, the new index will get the number of the first server-only index. The server-only index(es) will get their index number incremented, so they remain at the end of the index list.

Indexes can be renumbered using the DF_INDEX_NUMBER attribute.

Temporary Indexes

Temporary indexes are supported in DataFlex 19.0 and the SQL Database Drivers (SQL Server, DB2, and ODBC), version 6.2 or higher.

Temporary indexes are indexes that are created on the fly at runtime. They will only exist as long as the table is open. Temporary indexes will not be created on the database server.

Temporary indexes are created like regular indexes, but they must be created outside a Structure_Start…Structure_End block.

An index created outside a Structure_Start…Structure_End block will be a temporary index that only exists at runtime while the affected table is open.

Temporary indexes will have type DF_INDEX_TEMPORARY for DF_INDEX_SQL_TYPE.

A temporary index can be deleted outside a Structure_Start…Structure_End block with Delete_Index.

Example

This example opens the Customer table and creates an index consisting of three segments—the second column, fourth column, and RECNUM. In addition, it will be a batch index, with a case-insensitive segment and a descending segment.

Handle hTable
Integer iIndex
Open "Customer" as hTable
Structure_Start hTable "DATAFLEX"
Create_Index hTable at iIndex
// Set Index Attributes: 3 segments; batch type.
Set_Attribute DF_INDEX_NUMBER_SEGMENTS of hTable iIndex to 3
Set_Attribute DF_INDEX_TYPE of hTable iIndex to DF_INDEX_TYPE_BATCH
// Set the first segment of the index to be case insensitive.
// NOTE: If column used for this segment is not ASCII, the attribute
// will still be set, although it will be meaningless.
Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 1 to 2
Set_Attribute DF_INDEX_SEGMENT_CASE of hTable iIndex 1 to DF_CASE_IGNORED
// Set the second segment of the index to be in descending order.
Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 2 to 4
Set_Attribute DF_INDEX_SEGMENT_DIRECTION of hTable iIndex 2 to DF_DESCENDING
// Set the third segment of the index to use RECNUM—using 0 for
// column number represents RECNUM.
Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 3 to 0
Structure_End hTable DF_STRUCTEND_OPT_NONE

Example

This example creates a temporary index for a Customer table, then uses it to find a record using the vfind command, then via a DataDictionary Find.

Handle hTable
Integer iIndex
Open Customer
Move Customer.File_Number to hTable
Create_Index hTable at iIndex
// Set Index Attributes: 2 segments;
Set_Attribute DF_INDEX_NUMBER_SEGMENTS of hTable iIndex to 2
Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 1 to 4    // Column 4 == City
Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 2 to 1    // Column 1 == Customer_Number
Clear Customer
Move "Dallas" to Customer.City
Vfind hTable iIndex GT
Send Clear of Customer_DD
Move "Dallas" to Customer.City
Send Find of Customer_DD GT iIndex

Notes

  • If you want the new index to have more than one segment, be sure to set the DF_INDEX_NUMBER_SEGMENTS attribute. If this attribute is not set, it defaults to one, and any attempt to set more segments will result in an error (Bad parameter).

See Also

SQL Indexes