Selecting an index

Note indexes created automatically on all columns.

Default index

Note indexes created automatically on columns with UNIQUE or PRIMARY KEY constraint.

HG with UNIQUE enforced

Identify all columns used in a join predicate and choose the index type depending on the number of unique values.

HG

or

LF

Identify columns that contain a low number of unique values and do not already use multiple indexes.

LF

Identify columns that have a high number of unique values and that are part of a GROUP BY clause in a select list in a SELECT DISTINCT or DISTINCT COUNT.

HG

Identify columns that may be used in the WHERE clause of ad hoc join queries that do not already have HG or LF indexes.

HG

or

LF

Identify columns that have a high number of unique values and that will not be used with GROUP BY, SELECT DISTINCT or DISTINCT COUNT.

HNG

Identify pairs of columns with the same data type, precision, and scale that are likely to need frequent comparison.

CMP

Identify columns that contain a list of keywords or a URL.

WD

Identify columns of DATE, TIME, DATETIME, or TIMESTAMP that have a high number of unique values and that will not be used with GROUP BY, SELECT DISTINCT, or DISTINCT COUNT.

DATE, TIME, or DTTM

Look at any remaining columns and decide on additional indexes based on the number of unique values, type of query, and disk space. Also, for all columns, be sure that the index types you select allow the data type for that column.