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” in “Optimization for Cursors” in Performance and Tuning Series: Query Processing and Abstract Plans.
If you are creating an index on a table that will have a lot of insert activity, use fillfactor to temporarily minimize page splits, 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.
Internally, numerics compare slightly faster than strings.
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, by definition, variable-length.
Whenever possible, use fixed-length, nonnull 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.
