CREATE INDEX

This statement is used to create an index for a field, which can increase the efficiency queries when using SELECT ORDER BY and SELECT WHERE.

Syntax

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

Parameters

indexname: Specifies the name of the new index.
tablename: Specifies the name of the table on which to create the index.
fieldname: Specifies 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 following table shows the possible index attributes. They are used singly or in combination.

Attribute	Description
DESC	Sort the data into descending order. Default is ascending.
CASESENSITIVE	Sort capital letters before lower case. Default is insensitive.
UNKNOWNFIRST	Sort null values to the start of the table. Default is at the end.

The CASESENSITIVE and UNKNOWNFIRST statements work on Windows CE-based tables only and are generally not supported on other database systems. They may not be supported in future versions of Windows CE.

Example

Dim rs, i, L1
Set rs = CreateObject("adoce.recordset")
rs.open "create table indexme (f1 varchar)"
rs.open "indexme", "", 1, 3
rs.addnew "f1", "a"
rs.addnew "f1", "A"
rs.addnew "f1", "B"
rs.addnew "f1", "c"
rs.addnew "f1", "C"
rs.addnew "F1", "b"
rs.addnew "f1", "a"
rs.addnew "f1", "B"
rs.Close
rs.open "create index i1 on indexme (f1 CASESENSITIVE)"
rs.open "select f1 from indexme order by f1"
For i = 0 To rs.recordcount - 1
L1 = L1 & rs.fields("f1").Value
rs.MoveNext
Next
rs.Close
MsgBox L1
Set rs = Nothing