When a table has a clustered index, ASE makes sure that all rows are physically stored in the order defined by the columns on which you have the clustered index. There can only be one clustered index on a given table as ASE cannot store the data with two different orders.

You can either explicitely created using the following command:

create clustered index myclusteredindex on mytable(mycolumn)

Or by defining a primary constraint:

create table mytable(
	mycol1 int not null,
	mycol2 int not null,
	primary key (mycol1)
)

Not that if you defined a primary key, the index name is automatically generated and you cannot drop the index using the DROP INDEX command but need to use the DROP CONSTRAINT command.

If the table has a data lock scheme, the table will be reorganized when the clustered index is created but the order of rows will not be further updated. If the table has an allpages lock scheme, then ASE will make sure that the order is maintained.

Note that although clustered indexes (especially when defined through primary key constraints) are often created as unique indexes, it doesn’t have to be the case.

Reading from a table with allpages lock and a clustered index using the keys of the clustered index as criteria is almost always faster than without the clustered index. But writing to the table is slower since ASE needs to maintain the order. This can create huge performance issues when working with huge tables with many updates on the index keys or many inserts/deletes. In some cases (I observed a case on a table with 28 million entries), committing or rolling back changes on such a table can cause many physical IOs. If this is done in a transaction also involving updates on other tables this could cause many other connections to blocked. In the case I had observed, it took up to 30 minutes to finish some rollbacks. My assumption was that it is because ASE needs to reorganize the whole index which involves reading an writing many pages. In this case dropping the primary key constraints solved the problem. You can just replace the clustered index by a non-clustered one.

So I’d recommend in general not to use clustered index on huge allpages tables except if you are 100% sure that you need the extra read performance. The penalty while writing can be so huge that it cannot be compensated by the extra read speed.