Fast Projection (FP) Index

By default, the database engine automatically creates a Fast Projection (FP ) index on all columns. FP indexes optimize projections and allow the database engine to evaluate certain kinds of search conditions.

An FP index is an array of n fixed-length entries where n is the number of rows in the table. The IQ UNIQUE storage directive applied to the column determine whether the column loads as a flat FP or NBit. Flat FP indexes contain actual column cell values. NBit is a compression scheme that uses n bits to index the dictionary where the data is stored. All datatypes except LOB (both character and binary) and BIT datatypes may be NBit columns.

IQ UNIQUE

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.

Rebuilding FP Column Indexes

sp_iqindexmetadata displays details about column indexes, including the FP index type (Flat FP or NBIT), the distinct counts, IQ UNIQUE n value, and dictionary size.

sp_iqrebuildindex rebuilds Flat FP as NBIT, or NBIT as Flat FP. The index_clause can reset IQ UNIQUE n to an explicit value from '0' (to recast an NBIT column to Flat FP) up to the limits defined in the FP_NBIT_AUTOSIZE_LIMIT and FP_NBIT_LOOKUP_MB options. If the count exceeds the n value, and FP_NBIT_ENFORCE_LIMITS=ON, the operation rolls back. If the FP_NBIT_ENFORCE_LIMITS=OFF (default), the NBIT dictionary continues to grow.

Additional Information

  • Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_AUTO_LIMIT
  • Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_LOOKUP_MB
  • RReference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_ROLLOVER_MAX_MB
  • Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_ENFORCE_LIMIT
  • Reference: Building Blocks, Tables, and Procedures > System Procedures > Alphabetical List of System Stored Procedures > sp_iqrebuildindex
  • Reference: Building Blocks, Tables, and Procedures > System Procedures ยป Alphabetical List of System Stored Procedures > sp_iqindexmetadata
Parent topic: Index Types Comparison