Number of Unique Values in the Index

Indexes are optimized according to the number of unique (distinct) values they include.

IQ UNIQUE defines the expected cardinality of a column and determines whether the column loads as Flat FP or NBit. An IQ UNIQUE(n) value explicitly set to 0 loads the column as Flat FP. Columns without an IQ UNIQUE constraint implicitly load as NBit up to the limits defined by the FP_NBIT_AUTOSIZE_LIMIT and FP_NBIT_LOOKUP_MB options:

FP_NBIT_AUTOSIZE_LIMIT limits the number of distinct values that load as NBit
FP_NBIT_LOOKUP_MB sets a threshold for the total NBit dictionary size
FP_NBIT_ROLLOVER_MAX_MB sets the dictionary size for implicit NBit rollovers from NBit to Flat FP
FP_NBIT_ENFORCE_LIMITS enforces NBit dictionary sizing limits. This option is OFF by default

Using IQ UNIQUE with an n value less than the FP_NBIT_AUTOSIZE_LIMIT is not necessary. Auto-size functionality automatically sizes all low or medium cardinality columns as NBit. Use IQ UNIQUE in cases where you want to load the column as Flat FP or when you want to load a column as NBit when the number of distinct values exceeds the FP_NBIT_AUTOSIZE_LIMIT.

When the number of distinct values reaches certain levels, choose indexes according to the recommendations in this table.

Consideration Order
Number of Unique Values	Recommended Index Type
Below 1,000	LF (HG if table has <100,000 rows)
1000 and over	HG and/or HNG

Here are some examples of columns with different numbers of unique values:

Columns that hold marital status have just a few unique values (single, married, NULL)
Columns that hold state or province names have fewer than 100 unique values
Columns that hold date data probably have more than 100 but fewer than 65536 unique values
Columns that hold account numbers or social security numbers may have thousands or millions of unique numbers