Here are some other considerations for choosing indexes:
If an index key is unique, define it as unique so the optimizer knows immediately that only one row matches a search argument or a join on the key.
If your database design uses referential integrity (the references keyword or the foreign key...references keywords in the create table statement), the referenced columns must have a unique index, or the attempt to create the referential integrity constraint fails.
However, Adaptive Server does not automatically create an index on the referencing column. If your application updates primary keys or deletes rows from primary key tables, you may want to create an index on the referencing column so that these lookups do not perform a table scan.
If your applications use cursors, see “Index use and requirements for cursors”.
If you are creating an index on a table where there will be a lot of insert activity, use fillfactor to temporarily minimize page splits and improve concurrency and minimize deadlocking.
If you are creating an index on a read-only table, use a fillfactor of 100 to make the table or index as compact as possible.
Keep the size of the key as small as possible. Your index trees remain flatter, accelerating tree traversals.
Use small datatypes whenever it fits your design.
Numerics compare slightly faster than strings internally.
Variable-length character and binary types require more row overhead than fixed-length types, so if there is little difference between the average length of a column and the defined length, use fixed length. Character and binary types that accept null values are variable-length by definition.
Whenever possible, use fixed-length, non-null types for short columns that will be used as index keys.
Be sure that the datatypes of the join columns in different tables are compatible. If Adaptive Server has to convert a datatype on one side of a join, it may not use an index for that table.
See“Datatype mismatches and query optimization” on page 24 in Performance and Tuning: Optimizer for more information.
