Full Text Index Overview

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

A full text index contains location information about each significant word in a string field of a table. Some queries can use this information to run more efficiently and complete much sooner. These are queries that search for words that are embedded in the middle of string fields

A regular index on a long string field can help a query complete quickly only if the query searches for the word that is at the start of the string field. In X++ SQL this is done with the like keyword and the * wildcard character, such as in the code phrase like "*diamond*".

The following topics provide information about creating and using full text indexes.

The following table shows features and gives background details about full text indexes, and about the full text searches that use these indexes.

Features

Background Details

Microsoft SQL Server keywords.

The full text index feature of Microsoft Dynamics AX relies on the underlying database management software to implement the feature. The keyword FreeText is sent to the underlying SQL Server database system.

Space characters are treated as implicit Boolean OR operators.

There is a space character in the string parameter in call queryBRange4.value("diamond unfounded");. The space between diamond and unfounded is interpreted as a Boolean OR.

Stemming is supported.

The Microsoft Dynamics AX system relies on the underlying database management product for stemming functionality when you issue a query that uses a full text index. A stemmer generates a variety of inflectional forms of a word to increase the chance for a match.

Stemmers are specific to each language. For example, the stemmer for English cannot be used for German. In English, if the stemmer is given the word talk, it would generate similar words like talks, talked, and talking. For more information, see the documentation for your database management product, such as Oracle or Microsoft SQL Server.

Word breaking is supported.

The Microsoft Dynamics AX system relies on the underlying database management product for word breaker functionality when you insert data into a field that is part of a full text index. In English, if a word breaker is given website, it would generate the words web and site.

For more information, see the documentation for your database management product, such as Oracle or Microsoft SQL Server.

No wildcards are available.

There are no wildcard characters available for full text search. The * and ? wildcards that are available with the X++ like keyword are not available for full text search.

TableGroup property.

A table can have a full text index only if the TableGroup property is set to Main or Group on the table.

Maximum of one full text index per table.

You can create a maximum of one full text index per table.

Supported field types.

You can have multiple columns of type String in a single full text index. Also, the StringSize property of the string field can be set to a number or to (Memo).

See also

Indexes and Keys

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.