CREATE INDEX statement

CREATE INDEX syntax differs slightly among the three products:

• Adaptive Server Enterprise and Adaptive Server 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 Adaptive Server Anywhere because it allows, but ignores, the keywords FILLFACTOR, IGNORE_DUP_KEY, SORTED_DATA, IGNORE_DUP_ROW, and ALLOW_DUP_ROW.

• Adaptive Server Anywhere CREATE INDEX syntax supports the VIRTUAL keyword for use by its Index Consultant, but not for actual execution of queries.

• IQ supports seven specialized index types: LF, HG, HNG, DATE, TIME, DTTM, and WD. IQ also supports a CMP index on the relationship between two columns of identical data type, precision, and scale. 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,...)