Considerations When Using the CREATE INDEX Statement

CREATE INDEX syntax differs slightly between Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ.

Adaptive Server Enterprise and SQL Anywhere support clustered or nonclustered indexes, using the following syntax:
```
CREATE [UNIQUE] [CLUSTERED] INDEX name 
ON table (column,...)
ON dbspace
```
Adaptive Server Enterprise also allows the NONCLUSTERED keyword, but for both products the default is NONCLUSTERED.
Adaptive Server Enterprise CREATE INDEX statements work in SQL Anywhere because SQL Anywhere allows, but ignores, the keywords FILLFACTOR, IGNORE_DUP_KEY, SORTED_DATA, IGNORE_DUP_ROW, and ALLOW_DUP_ROW.
SQL Anywhere CREATE INDEX syntax supports the VIRTUAL keyword for use by its Index Consultant, but not for actual query executions.
Sybase IQ supports seven specialized index types: LF, HG, HNG, DATE, TIME, DTTM, and WD. Sybase IQ also supports a CMP index on the relationship between two columns of identical data type, precision, and scale. Sybase IQ defaults to creating an HG index unless the index type is specified in the CREATE INDEX statement:
```
CREATE [UNIQUE] [type] INDEX name 
ON table (column,...)
```

Note: Sybase IQ also supports CREATE JOIN INDEX, which lets you create a prejoined index on a certain set of columns that are joined consistently and frequently in queries.