Full text indexes in AX 2012

Continue reading

Dynamics AX 2012 uses full-text indexes maintained by Full-Text Engine for SQL Server.

Full-text indexes allow for quick searches over large volume of text data (Dynamics AX Memo fields) or even documents. You can build indexes over columns that store up to 2GB of data (nvarchar(max)).

Full-text index stores information about significant words and their location within one or more columns of a table. It is possible to define an SQL thesaurus in order to enable searches of words of similar meaning, e.g. you can search against word ‘vehicle’ and SQL will return records containing ‘cars’ and ‘bike’.

Configuration procedure

Step 1

In AOT set TableGroup property to Main.

Table properties.

TStep 2

Under TableName > Full Text Indexes create new Full Text Index. Give it appropriate name and drop a field to this index. In the presented example field Notes of string size (Memo) is used.

Create Full Text Index.

Step 3

Optional: please observe the full text catalogue created in AX Business Database.

Full Text Catalogs in AX Business Database.
Full-Text Catalog Properties.

Step 4

Please edit a thesaurus for appropriate language, you will find them in

[SQL installation dir]\MSSQL11.MSSQLSERVER\MSSQL\FTData. For EN-US (1033) file name is tsenu.xml.

Find the contents used in this example below

<XML ID="Microsoft Search Thesaurus">

 <thesaurus xmlns="x-schema:tsSchema.xml">













Step 5

Load thesaurus to SQL by executing following T-SQL script

USE MicrosoftDynamicsAX;

EXEC sys.sp_fulltext_load_thesaurus_file 1033;


Usage in X++ source code

To use full text indexes in X++ source code you need to create QueryBuildRange object with type FullText:

static void DevI_Ch2_FullTextIndex_Demo(Args _args)


   DevI_Ch2_FullTextIndexed devI_Ch2_FullTextIndexed;

   Query query;

   QueryRun queryRun;

   QueryBuildDataSource queryBuildDataSource;

   QueryBuildRange queryBuildRange;

   query = new Query();

   queryBuildDataSource = query.addDataSource(tableNum(DevI_Ch2_FullTextIndexed));

   queryBuildRange = queryBuildDataSource.addRange(fieldNum(DevI_Ch2_FullTextIndexed, Notes),

       1, QueryRangeType::FullText);


   queryRun = new QueryRun(query);

   while (queryRun.next())


       devI_Ch2_FullTextIndexed = queryRun.getNo(1);




Please observe that I search by ‘vehicle’ phrase which does not exist in the table. But thanks to thesaurus relevant records are found. If no thesaurus is employed full text searches will work but no similar words will be found.

Find data used in the example:

Table browser.

The outcome from script DevI_Ch2_FullTextIndex_Demo:


Please note that full text index rebuilding process takes place after transactional part of a T-SQL command. This means that when you query database immediately after inserting or updating, the engine will use ‘old’ full text index (will not find records or will find records that are not relevant anymore). Full text index is synchronised with database in asynchronous mode.

More articles
Explore our blog

What can we do for you?

We'd love to hear about your project. Our team will get back to you within two working days.

Thank you for inquiry, we’ve passed it to our sales department, our representative will reach you back in his earliest convenience.
Oops! Something went wrong while submitting the form.