INDEXHISTOGRAMS System Table Description

Description

A pseudo table containing one row for every index in your database. This table represents data related to the index histogram. It is currently almost an exact subset of INDEXSTATISTICS with the exception of FieldCount, but this is subject to change in the future.

Client applications should never contain code that accesses SYSTEM pseudo tables as they are subject to, and furthermore likely to, change from release to release.

Fields

Field Type Description

OBJECTID

bigint

The ID for the database object

CATALOGID

bigint

The catalog ID for the database object

HISTOGRAMID

integer

The ID for the histogram, unique to the table

FIELDCOUNT

integer

The number of fields in the index

MAXRESOLUTION

bigint

The maximum number of histogram buckets. Index histograms are one of the most important parts of index statistics. By default, the number of histogram buckets created is 10 for Version 1 statistics and 256 for Version 2 statistics. Raising this number (see CREATE INDEX) will make more precise statistics available to the query optimizer.

Indexes

None

Example

In the following example, we are referencing the table SCORING created by SQL QuickStart (see Running the SQL QuickStart). This table has a primary key, SCORING..PRIMARY_KEY, which is defined over five columns.

SELECT s.tablename,
    s.indexname,
    h.fieldcount,
    h.maxresolution as MAXRES,
    h.histogramid as HIST,
    h.objectid as OBJ,
    h.catalogid as CAT
        FROM system.indexstatistics s, system.indexhistograms h
WHERE s.tablename='SCORING' and
    s.objectid = h.objectid and
    s.catalogid = h.catalogid
ORDER BY s.tablename, s.indexname;
 TABLENAME       INDEXNAME       FIELDCOUNT  MAXRES HIST OBJ CAT
 ---------- -------------------- ----------- ------ ---- --- ---
  SCORING   SCORING..PRIMARY_KEY      5        10    5    1  78