Adaptive Server provides two general types of indexes that can be created at the table or at the partition level.
Clustered indexes, where the data is physically stored in the order of the keys on the index:
For allpages-locked tables, rows are stored in key order on pages, and pages are linked in key order.
For data-only-locked tables, indexes are used to direct the storage of data on rows and pages, but strict key ordering is not maintained.
Nonclustered indexes, where the storage order of data in the table is not related to index keys
You can create only one clustered index on a table or partition because there is only one possible physical ordering of the data rows. You can create up to 249 nonclustered indexes per table.
A table that has no clustered index is called a heap. The rows in the table are in no particular order, and all new rows are added to the end of the table. Chapter 2, “Data Storage,” in Performance and Tuning Series: Physical Database Tuning discusses heaps and SQL operations on heaps.
For partitioned tables, indexes may be either local or global (see “Indexes and partitions”).
Function-based indexes are a type of nonclustered index which use one or more expressions as the index key. See the Transact-SQL Users Guide for more on creating function-based indexes. See also Chapter 6, “Indexing for Concurrency Control,” for information on when to use function-based indexes.
