Placing an index on a column often makes the difference between a quick response to a query and a long wait.
However, building an index takes time and storage space. For example, nonclustered indexes are automatically re-created when a clustered index is rebuilt.
Additionally, inserting, deleting, or updating data in indexed columns takes longer than in unindexed columns. However, this cost is usually outweighed by the extent to which indexes improve retrieval performance.
When determining whether or not to create an index, following these general guidelines:
If you plan to make manual insertions into the IDENTITY column, create a unique index to ensure that the inserts do not assign a value that has already been used.
A column that is often accessed in sorted order, that is, specified in the order by clause, should genereally be indexed, so that Adaptive Server can take advantage of the indexed order.
Columns that are regularly used in joins should always be indexed, since the system can perform the join faster if the columns are in sorted order.
The column that stores the primary key of the table often has a clustered index, especially if it is frequently joined to columns in other tables. Remember, there can be only one clustered index per table.
A column that is often searched for ranges of values is often a good choice for a clustered index. Once the row with the first value in the range is found, rows with subsequent values are guaranteed to be physically adjacent. A clustered index does not offer as much of an advantage for searches on single values.
In some cases, indexes are not useful:
Columns that are seldom or never referenced in queries do not benefit from indexes, since the system seldom has to search for rows on the basis of values in these columns.
Columns that have many duplicates, and few unique values relative to the number of rows in the table, receive no real advantage from indexing.
If the system does have to search an unindexed column, it does so by looking at the rows one by one. The length of time it takes to perform this kind of scan is directly proportional to the number of rows in the table.
