Full text indexes in AX 2012

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.

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.

Step 3

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

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">

<diacritics_sensitive>0</diacritics_sensitive>

       <expansion>

         <sub>vehicles</sub>

         <sub>vehicle</sub>

         <sub>bike</sub>

         <sub>bicycle</sub>

         <sub>bicycles</sub>

         <sub>car</sub>

         <sub>cars</sub>

       </expansion>

   </thesaurus>

</XML>

Step 5

Load thesaurus to SQL by executing following T-SQL script

USE MicrosoftDynamicsAX;

EXEC sys.sp_fulltext_load_thesaurus_file 1033;

GO

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);

   queryBuildRange.value('vehicle');

   queryRun = new QueryRun(query);

   while (queryRun.next())

   {

       devI_Ch2_FullTextIndexed = queryRun.getNo(1);

       info(devI_Ch2_FullTextIndexed.Id);

   }

}

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:

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.

‍