Full-text index structure

A proper understanding of the structure of a full-text index can help you understand how the Microsoft full-text Engine for SQL Server (MSFTESQL) service works. The following excerpt lists the two columns and three rows of the Document table in Adventure Works, the documentid column and the Title column.

In the following example, we assume that a full-text index has been created on the Title column.

DocumentID	Title
1	Crank Arm and Tire maintenance
2	Front Reflector Bracket and Reflector Assembly 3
3	Front Reflector Bracket Installation

The following table fragment shows the content of the full-text index created on the Title column of the Document table.

Attention:
The full-text index contains more information than is displayed in this table. The table below is for illustrative purposes only.

Keyword	ColId	DocId	OCC
Crank	1	1	1
Arm	1	1	2
Tire	1	1	4
Maintenance	1	1	5
Front	1	2	1
Front	1	3	1
Reflector	1	2	2
Reflector	1	2	5
Reflector	1	3	2
Bracket	1	2	3
Bracket	1	3	3
Assembly	1	2	6
3	1	2	7
Installation	1	3	4

The Keyword column contains a representation of the individual tags that were extracted when the index was created. The word breaker determines the words that make up the tag.

The ColId column contains values that correspond to a specific table and column that creates a full-text index.

The DocId column contains a four-byte integer value that maps to a specific full-text key value in a full-text indexed table. The DocId values that meet the search criteria are passed from the MSFTESQL service to the database engine, which in the database engine is mapped to full-text key values in the base table being queried.

The Occ column contains an integer value. For each DocId value, there is a list of positional values that correspond to the relative character offsets of a particular keyword in the DocId value. Position values are used to determine a phrase or proximity match, such as a phrase with adjacent position values. They are also used to calculate correlation scores, such as when a score may be used to position a keyword in DocId .

Full-text index structure