table without primary key or unique index

[Former Member]

I have a table has no primary key or unique index, but there is one non-unique index, what's possible problem for this table? Big impact on performance on insert/select?

if so, how to add a id column(like MS Sql server identity column) to this table?

[former_member188958]

Without a clustered index the table is known as a "heap table". All inserts occur on the last page of the table, making it a hot spot if there are a lot of inserts.  The impact can be reduced by partitioning the table (you then get a less-hot spot on the last page of each partition as the contention is spread (randomly by roundrobin partitioning) among the partitions).  It can also be handled by creating a clustered index on a column with a fairly random distribution, ideally also one that is useful for range queries.

Selects have to scan the entire table if there is no suitable index, but that is a more general issue, not quite specific to not having unique or primary key indexes.

You can add an identity column with ALTER TABLE.

see Adding an IDENTITY column to a source table

If the table is very large, this can generate a lot of log records.

Creating a unique index on the identity column will only help SELECTs that are searching for a particular value in the identity field.

Creating a clustered index on the identity column will not help with the hot-spot issue as ASE will try to place the row just after the one with the previously-generated identity value.