B.8 CREATE INDEX

This statement creates an index for a field.

CREATE INDEX indexname ON tablename (fieldname [DESC] [CASESENSITIVE] [UNKNOWNFIRST])

Parameters

indexname

The name of a new index.

tablename

The name of the table for which to create the index.

fieldname

The name of the field for which to create the index. Only one field is indexed at a time, and only one index is created per field. The index can be given additional properties by specifying an index attribute after the field name.

Return Values

E_OUTOFMEMORY
DB_E_ERRORSINCOMMAND
DB_E_DUPLICATEINDEXID
DB_E_BADCOLUMNID
DB_E_NOTABLE

Remarks

The CREATE INDEX statement can increase the efficiency of SQL queries that use SELECT ORDER BY and SELECT WHERE statements.

The following table shows the possible index attributes. They are used singly or in combination.

Attribute	Description
DESC	Sorts the data in descending order. The default is ascending.
CASESENSITIVE	Sorts capital letters before lower case. The default is case insensitive.
UNKNOWNFIRST	Sorts NULL to the start of the table. The default is NULL at the end of the table.

The CASESENSITIVE and UNKNOWNFIRST statements work on Windows® CE-based tables only and generally are not supported on other database systems.

An SQL statement cannot be longer than 2,048 characters. A longer statement causes an error.