Clustered,Non-clustered Indexes

Heap = the data in a table, rows in chronological (insert) order

Index = B-Tree structure of keys, used to speed up the access to data rows.
Except for APL/CI, it is detached (separate allocation) from the heap.

Clustered = A special type of Index for APL tables only; where the Index is 'clustered' with the data; the leaf-level of the Index is the row (and therefore every access to data is one less than with a Nonclustered index).____Not available for DPL/DRL

Nonclustered = the ordinary Index, any index other than Clustered, detached from the Heap.
For performnce reasons, these should be placed on a different segment to the Heap or CI.

Placement : A special form of Nonclustered, the attempt to provide a semblance of 'clustering' for DPL/DRL tables only. Lives on the same segment as the Heap. The physical order of rows is attempted (it exists after a fresh create_index or reorg_rebuild, but it is lost after a few insert/deletes)

**The term Clustered when used with DPL/DRL, is incorrect**.

**Unfortunately the manuals use the incorrect term**

Unique = the indexed column(s) uniquely identify a single row. Prevents another row with the same values as the indexed column(s) being inserted.

Nonunique = the indexed column(s) identify more than one row.

Composite Key (not composite index) = The Key has more than one column.

APL/DPL/DRL are lockschems for the tables.

Credit goes to Derek Asirvadem for his clear explanation about indexes on sybase forums...

Heap = the data in a table, rows in chronological (insert) order

Index = B-Tree structure of keys, used to speed up the access to data rows.
Except for APL/CI, it is detached (separate allocation) from the heap.

Clustered = A special type of Index for APL tables only; where the Index is 'clustered' with the data; the leaf-level of the Index is the row (and therefore every access to data is one less than with a Nonclustered index).____Not available for DPL/DRL

Nonclustered = the ordinary Index, any index other than Clustered, detached from the Heap.
For performnce reasons, these should be placed on a different segment to the Heap or CI.

Placement : A special form of Nonclustered, the attempt to provide a semblance of 'clustering' for DPL/DRL tables only. Lives on the same segment as the Heap. The physical order of rows is attempted (it exists after a fresh create_index or reorg_rebuild, but it is lost after a few insert/deletes)

**The term Clustered when used with DPL/DRL, is incorrect**.

**Unfortunately the manuals use the incorrect term**

Unique = the indexed column(s) uniquely identify a single row. Prevents another row with the same values as the indexed column(s) being inserted.

Nonunique = the indexed column(s) identify more than one row.

Composite Key (not composite index) = The Key has more than one column.

APL/DPL/DRL are lockschems for the tables.

Credit goes to Derek Asirvadem for his clear explanation about indexes on sybase forums...