Creating Clustered Indexes

A table “follows” its clustered index. When you create a table, using the on segment_name extension to create clustered index, the table migrates to the segment where the index is created.

If you create a table on a specific segment, then create a clustered index without specifying a segment, the SAP ASE server moves the table to the default segment when it creates the clustered index there.

Because text, unitext, and image data is stored in a separate page chain, creating a clustered index with on segment_name does not move text and image columns.

To create a clustered index, the SAP ASE server duplicates the existing data; the server deletes the original data when the index is complete. Before creating a clustered index, use sp_spaceused to make sure that the database has at least 120 percent of the size of the table available as free space.

The clustered index is often created on the table’s primary key (the column or columns that uniquely identify the row). You can record the primary key in the database (for use by front-end programs and sp_depends) using sp_primarykey.

To allow duplicate rows in a clustered index, specify allow_dup_row.