The options available depend upon the database platform version.
● Unique: (UNIQUE) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created where table rows can have duplicate values in the columns that define the index.
● Type: Sybase IQ uses HG or LF indexes to generate better or faster query plans to execute the query. The HG or LF index may also be appropriate for a DATE, TIME, DATETIME, or TIMESTAMP column, if you are evaluating equality predicates against the column. An LF index is also recommended, if you frequently use the column in the GROUP BY clause and there are less than 1000 distinct values, for example, less than three years of dates.
• HG: Use an HG (High Group) index when the column will be used in a join predicate or the column has more than 1,000 unique values. The High_Group index is commonly used for join columns with integer data types because it handles GROUP BY efficiently. Each foreign key columns requires its own HG index; however, if a join index exists, the same column cannot have both an explicitly created HG index and a foreign key constraint.
• LF: LF is the fastest index in Sybase IQ. Use an LF (Low Fast) index when a column has less than 1,000 unique values or a column has less than 1,000 unique values and is used in a join predicate. This index is ideal for columns that have a few unique values (under 1,000) such as sex, Yes/No, True/False, number of dependents, and wage class.Never use an LF index for a column with 10,000 or more unique values. If the table has less than 25,000 rows, use an HG index, which requires fewer disk I/O operations for the same operation.
● Clustered: (CLUSTER) Select to create a clustered index. A clustered index specifies that the rows of the table should be inserted sequentially on data pages, which generally requires less disk I/O to retrieve the data. Generally, the clustered index should be on a column that monotonically increases, such as an identity column, or some other column where the value is increasing, and is unique. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.
● Ignore Dup Keys: (IGNORE_DUP_KEY) Specifies that if you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement), the row that causes the duplicates is not added or, in the case of an update, is discarded.
● Sorted or Sorted Data: (SORTED_DATA) Specifies to sort the data when the index is rebuilt.
● Non-Unique Clustered Options: Choose how you want to handle duplicate rows.
• Ignore Dup Rows: (IGNORE_DUP_ROW) Eliminates duplicate rows from a batch of data and cancels any insert or update that would create a duplicate row, but does not roll back the entire transaction.
• Allow Dup Rows: (ALLOW_DUP_ROW) Allows you to create a new, non-unique clustered index on a table that includes duplicate rows. If a table has a non-unique clustered index that was created without the allow_dup_row option, you cannot create new duplicate rows using the insert or update command.
• None: If neither Ignore Dup Rows or Allow Dup Rows has been selected, when you attempt to create an index on a table that has duplicate rows the creation fails, and the insertion fails If you try to insert a duplicate row to an indexed table.