This statement is used to create an index for a field.
Syntax
CREATE INDEX indexname ON tablename (fieldname [DESC] [CASESENSITIVE] [UNKNOWNFIRST])
Parameters
indexname
Specifies the name of a 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
Remarks
The CREATE INDEX statement can increase the efficiency of SQL queries that use SELECT ORDER BY and SELECT WHERE.
The following table shows the possible index attributes. They are used singly or in combination.
| Attribute | Description |
| DESC | Sort the data in descending order. Default is ascending. |
| CASESENSITIVE | Sort capital letters before lower case. Default is case insensitive. |
| UNKNOWNFIRST | Sort null values to the start of the table. Default is null values at the end. |
The CASESENSITIVE and UNKNOWNFIRST statements work on Windows CE-based tables only and are generally not supported on other database systems.
Example
The following code example shows how to use the CREATE INDEX statement in a SQL query.
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