See the following table for implications of the CREATE TABLE option IQ UNIQUE.
IQ UNIQUE 256 or less |
IQ UNIQUE 65536 or less |
IQ UNIQUE 16777216 or less |
IQ UNIQUE unspecified or greater than 16777216 |
|---|---|---|---|
Storage optimized for small number of unique values |
Storage optimized for medium number of unique values |
Storage optimized for 3-byte FP indexes |
Storage optimized for large number of unique values |
Faster query performance, less main IQ store space required |
Faster query performance, less main IQ store space required |
Faster query performance, less main IQ store space required |
Queries may be slower |
Need a small amount of extra cache for IQ temporary store for loads |
Need extra cache for IQ temporary store for loads. The amount depends on the number of unique values and the data type. |
Need significant extra cache for IQ temporary store for loads. The amount depends on the number of unique values and the data type. |
No extra cache needed for loads |
Loads may be slower if you have numerous columns with IQ UNIQUE <256 |
Loads may be slower |
Loads may be slower |
Loads are faster |
When MINIMIZE_STORAGE is ON, it is equivalent to specifying IQ UNIQUE 255 for all new columns.
MINIMIZE_STORAGE defaults to OFF. For details, see Reference: Statements and Options > Database Options > General Database Options > MINIMIZE_STORAGE Option.
If you estimate IQ UNIQUE incorrectly, there is no penalty for loads; the Optimizer simply uses the next larger index.
For queries, if you estimate IQ UNIQUE incorrectly and you have an HG, LF, or storage-optimized default index, the Optimizer ignores the IQ UNIQUE value and uses the actual number of values in the index. If you do not have one of these indexes and your estimate is wrong by a significant amount (for example, if you specify IQ UNIQUE 1000000 when the actual number of unique values is 12 million), query performance may suffer.
To change the value of IQ UNIQUE for an existing index, run the sp_iqrebuildindex procedure. For details, see Reference: Building Blocks, Tables, and Procedures > System Procedures.
IQ UNIQUE (count) gives an approximation of the number of distinct values that can be in a given column. Each distinct value can appear many times. For example, in the employee table, a limited set of distinct values could appear in the state column, but each of those values could appear in many rows.
By contrast, when you specify UNIQUE or PRIMARY KEY, each value can occur only once in that column. For example, in the employee table, each value of ss_number, the employee's social security number, can occur just once throughout that column. This uniqueness extends to NULL values. Thus, a column specified as UNIQUE must also have the constraint NOT NULL.