With a clustered index, Adaptive Server sorts rows on an ongoing basis so that their physical order is the same as their logical (indexed) order. The bottom or leaf level of a clustered index contains the actual data pages of the table. Create the clustered index before creating any nonclustered indexes, since nonclustered indexes are automatically rebuilt when a clustered index is created.
There can be only one clustered index per table. It is often created on the primary key—the column or columns that uniquely identify the row.
Logically, the database’s design determines a primary key. You can specify primary key constraints with the create table or alter table statements to create an index and enforce the primary key attributes for table columns. You can display information about constraints with sp_helpconstraint.
Also, you can explicitly define primary keys, foreign keys, and common keys (pairs of keys that are frequently joined) by using sp_primarykey, sp_foreignkey, and sp_commonkey. However, these procedures do not enforce key relationships.
You can display information about defined keys with sp_helpkey and about columns that are likely join candidates with sp_helpjoins. See the Reference Manual: Procedures. For a definition of primary and foreign keys, see Chapter 20, “Triggers: Enforcing Referential Integrity.”
With a nonclustered index, the physical order of the rows is not the same as their indexed order. The leaf level of a nonclustered index contains pointers to rows on data pages. More precisely, each leaf page contains an indexed value and a pointer to the row with that value. In other words, a nonclustered index has an extra level between the index structure and the data itself.
Each of the up to 249 nonclustered indexes permitted on a table can provide access to the data in a different sorted order.
Finding data using a clustered index is almost always faster than using a nonclustered index. In addition, a clustered index is advantageous when many rows with contiguous key values are being retrieved—that is, on columns that are often searched for ranges of values. Once the row with the first key value is found, rows with subsequent indexed values are guaranteed to be physically adjacent, and no further searches are necessary.
If neither the clustered nor the nonclustered keyword is used, Adaptive Server creates a nonclustered index.
Here is how the titleidind index on the title_id column of the titles table is created. To try this command, first drop the index:
drop index titles.titleidind
Then, create the clustered index:
create clustered index titleidind on titles(title_id)
If you think you will often want to sort the people in the friends_etc table, which you created in Chapter 8, “Creating Databases and Tables,” by postal code, create a nonclustered index on the postalcode column:
create nonclustered index postalcodeind on friends_etc(postalcode)
A unique index does not make sense here, since some of your contacts are likely to have the same postal code. A clustered index would not be appropriate either, since the postal code is not the primary key.
The clustered index in friends_etc should be a composite index on the personal name and surname columns, for example:
create clustered index nmind on friends_etc(pname, sname)
