Full-Text Indexes

Full-text indexes enable efficient keyword searches across large text columns. Unlike regular indexes — which match exact values or prefixes — full-text indexes break text into individual words (tokens) and build an inverted index that supports word-level searching, inflectional forms, and proximity queries.

Use full-text indexes when your application needs to search within free-text fields such as descriptions, comments, or notes, where LIKE '%keyword%' queries would be prohibitively slow.

---

Prerequisites

Full-text indexing relies on the SQL Server Full-Text Search feature. This must be installed and enabled on the database server. In D365 F&O cloud-hosted environments, full-text search is available by default. For on-premises or development VMs, verify that the feature is installed in SQL Server.

A full-text catalog must exist in the database. D365 creates a default full-text catalog during deployment. Tables with full-text indexes are automatically registered in this catalog during database synchronization.

---

How It Works

1. Define the full-text index on the table in the AOT, specifying which fields to include.
2. Database synchronization registers the index with SQL Server's full-text engine.
3. The full-text engine tokenises the content of the indexed fields and builds an inverted word index.
4. Application code searches using the CONTAINS or FREETEXT SQL predicates (typically via X++ query objects or direct SQL).

Change Tracking

The ChangeTracking property controls how the full-text index stays up to date:

Mode	Behaviour
Auto	SQL Server automatically updates the full-text index when data changes. This is the default and recommended setting.
Manual	The full-text index is only updated when an explicit population is triggered. Use this for very large tables where incremental updates would cause performance issues during peak hours.

tip

For most tables, leave ChangeTracking set to Auto. Manual tracking is only appropriate for tables with millions of rows and infrequent search requirements, where you can schedule population during off-peak windows.

---

Limitations

• Column types — only String (nvarchar) fields can be included. Numeric, date, and enum fields are not supported.
• Performance — full-text index population can be resource-intensive on very large tables. Monitor SQL Server resource usage after enabling.
• Query syntax — standard X++ select statements do not support full-text predicates. You must use query objects with CONTAINS/FREETEXT or direct SQL (Statement class).
• One per table — each table can have at most one full-text index, but that index can include multiple fields.

---

Properties

8/8 properties

Property	Display Name	Type	Description
Full-Text Index
Name	Name	String	The name of the element.
Tags	Tags	String	Tags for this element separated by semicolon.
ConfigurationKey	Configuration Key	String	The configuration key assigned to the item.
ChangeTracking	Change Tracking	AutoManual_ITxt	Configure full-text indexing to be automatic or manual. Values: Auto (0), Manual (1).
Full-Text Index Field
Name	Name	String	The name of the element.
DataField	Data Field	String	The table field included in this full-text index.
IncludedColumn	Included Column	NoYes	Whether this field is an included (non-key) column. Values: No (0), Yes (1).
Optional	Optional	NoYes	Whether this index field is optional. Values: No (0), Yes (1).