Creates an index for a table.

CREATE [UNIQUE | BITMAP] INDEX index-name
 ON [TABLE] table-name (column-name [ASC | DESC], ...)

CREATE INDEX creates a sorted index on the specified columns of the named table. To create an index, you must have the ALTER privilege for table access in your user definition.

The name of an index must be unique within a given table. Index names follow identifier conventions, subject to the restrictions below. By default, index names are simple identifiers. An index name should not exceed 128 characters.

Caché uses the name you supply (which it refers to as the “SqlName”) to generate a corresponding index name. An index name contains only alphanumeric characters (letters and numbers) and is a maximum of 31 characters in length. To generate this index name, Caché first strips punctuation characters from the name you supply, and then generates a unique identifier of 31 (or less) characters, substituting a capital letter for the 31st character when needed to create a unique index name.

What happens when you try to create an index with the same name as an existing index is described below.

What happens when you try to create an index that has the same name as an existing index for that table depends on a setting of the Caché Configuration Manager. Go to the Advanced tab and list the SQL options to display the current value of: Allow DDL CREATE INDEX for Existing Index. The default is “No.” By default, Caché rejects an attempt to create an index with the name of an existing index for that table and returns an SQLCODE -324 error. This is the recommended setting for this option.

However, if this option is set to “Yes,” Caché deletes the existing index from the class definition and then recreates it by performing the CREATE INDEX. It deletes the named index from the table specified in CREATE INDEX. This option permits the delete/recreate of a UNIQUE constraint index (which cannot be done using a DROP INDEX command). To delete/recreate a primary key index, refer to the ALTER TABLE command.

However, even if this option is set to allow the recreating of an existing index, you cannot recreate an IDKEY index if the table contains data. Attempting to do so returns an SQLCODE -324 error.

Using the UNIQUE keyword, you can specify that each record in the index has a unique value. More specifically, this ensures that no two records within the index (and hence in the table that contains the index) can have the same collated value. By default, most indices use an upper case collation (to make searches case-insensitive). In this case, the values “Smith” and “SMITH” are considered to be equal and not unique.

Using the BITMAP keyword, you can specify that this index will be a bitmap index. A bitmap index maintains a list of row ids corresponding to its indexed values using a bitmap (a compressed set of binary digits). The SQL Query Processor can take advantage of bitmap indices to perform restrictions using set operations; that is, it can use logical AND and OR operations to execute table restrictions (WHERE clauses). There is no significant difference in the performance of INSERT, UPDATE, or DELETE operations between using bitmap and regular indices. You can only define a bitmap index for tables that use default (%CacheStorage) structure and have system-assigned numeric row ids (i.e., do not use the IDKEY mechanism). If you use DDL (as opposed to using class definitions) to create a table, then it will meet these requirements and you can make use of bitmap indices.

The following embedded SQL example creates a table named Fred, and then creates an index named "FredIndex" (by stripping out the punctuation from the supplied name “Fred_Index”) on the Lastword and Firstword columns of the Fred table.

   SET %msql="_SYSTEM"
   &sql(CREATE TABLE Fred (
  TESTNUM     INT NOT NULL,
  FIRSTWORD   CHAR (30) NOT NULL,
  LASTWORD    CHAR (30) NOT NULL,
  CONSTRAINT FredPK PRIMARY KEY (TESTNUM))
  )
  IF SQLCODE=0 { WRITE !,"Table created" }
  ELSEIF SQLCODE=-201 { WRITE !,"Table already exists" }
  ELSE { WRITE !,"SQL table create error code is: ",SQLCODE
         QUIT }
  &sql(CREATE INDEX Fred_Index
       ON TABLE Fred
       (LASTWORD,FIRSTWORD))
  IF SQLCODE=-324 {
      WRITE !,"Index already exists" 
      QUIT }
  ELSEIF SQLCODE=0 { WRITE !,"Index created" }
  ELSE { WRITE !,"SQL index create error code is: ",SQLCODE 
         QUIT }

The following example creates an index, named “CityIndex” on the City column of the Staff table:

CREATE INDEX CityIndex ON Staff (City)

The following example creates an index, named “EmpIndex” on the EmpName column of the Staff table. The UNIQUE constraint is used to avoid having rows with identical values in the columns:

CREATE UNIQUE INDEX EmpIndex ON TABLE Staff (EmpName)

The following example creates a bitmap index, named “SKUIndex” on the SKU column of the Purchases table. The BITMAP keyword indicates that this is a bitmap index:

CREATE BITMAP INDEX SKUIndex ON TABLE Purchases (SKU)

DROP INDEX