Enable optimized text search on table fields

APPLIES TO: Business Central 2024 release wave 2 (v25) and later.

Business Central includes an optimized text search feature on table fields, which uses the full-text search feature in SQL Server and Azure SQL Database databases. Optimized text search lets users and applications run full-text queries against character-based data in tables. This article explains how to enable optimized text search on table fields and how you can use it to filter data in AL.

In the client, optimized text search is used in various search functionalities, such as in the company-wide data search and on lists that include optimized text search fields, referred to as modern search. Learn more in Search for a record in company data and Search lists. By enabling optimized text search on fields in extensions, you can significantly optimize data search in your AL code.

Enable optimized text search on fields

To enable optimized text search on field, set the OptimizeForTextSearch property to true. The following example shows how text search is enabled on the field level:

table 50100 Address
{
    Caption = 'Sample table';

    fields
    {
        field(1; Address; Text[50])
        {
            Caption = 'Address retrieved by Service';
            OptimizeForTextSearch = true;
        }
        field(2; Locality; Text[30])
        {
            Caption = 'Locality retrieved by Service';
            OptimizeForTextSearch = true;
        }
        field(3; "Town/City"; Text[30])
        {
            Caption = 'Town/City retrieved by Service';
            OptimizeForTextSearch = true;            
        }
        field(4; County; Text[30])
        {
            Caption = 'County retrieved by Service';
            OptimizeForTextSearch = true;
        }
        field(5; IsValidated; Boolean)
        {
            Caption = 'Address validated yet?';
        }        
    }

You can use the OptimizedForTextSearch property on the Field virtual table. You can also use the IsOptimizedForTextSearch() method on FieldRef type variables of the FieldRef data type.

Filter syntax for optimized text search

To use optimized text search in AL, you can add the && operator when setting a filter, as illustrated in the following example:

// Optimized text search without wildcard
Rec.SetFilter(Rec.Field, '&&' + SearchString);

You can also include wildcards in full-text search by adding the * symbol to the search clause as illustrated in the following example:

// Optimized text search with wildcard
Rec.SetFilter(Rec.Field, '&&' + SearchString + '*' ); 

Optimized text search semantics

Optimized text search differs from the traditional wildcard search using the '*' symbol in the following ways:

• Optimized text search is always case insensitive, where wildcard search is case sensitive unless either the dataset collation is insensitive or the @ operator is prefixed.
• Optimized text search is always accent insensitive, where wildcard search is accent sensitive unless either the dataset collation is insensitive or the @ operator is prefixed.
• Optimized text search searches for words within fields, where wildcard search for letters within fields. That means full-text search can find words or prefixes of words within a field, but wildcard search can find arbitrary substrings within words. Learn more in the following example.

Example

Consider a database that includes the item london swivel chair. The following table demonstrates different queries on the item and indicates whether there's a match.

Install full-text search on the database (on-premises only)

To support optimized text search in Business Central on-premises, the SQL Server instance must include the full-text search feature.

The SQL server instance that hosts your Business Central database must include the Full-text and Semantic Extractions for Search feature. The version 25 Microsoft base application requires this feature; however, it isn't installed by default.

1. To verify whether full-text search is installed:

   • Using Transactional-SQL

     Execute the following query on the SQL database instance:

     SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;
     

     If the query returns 0, then full-text search isn't installed; if 1, then it's installed.

   • Using SQL Server Management Studio

     In Object Explorer, right-click a server instance, select Properties, then select Advanced. If the Default Full-Text Language property is present, then full-text search is installed; otherwise, it isn't.

   Learn more at Viewing or Changing Server Properties for Full-Text Search

2. To install full-text search, add it as a feature by using the SQL Server Installation Center.

   You need to access the installation media or folder for your SQL Server version, and then run set.exe to start the SQL Server Installation Center. Follow the instructions at Add Features to an Instance of SQL Server. When you get to the Feature Selection page, choose Full-text and Semantic Extractions for Search.

   Tip

   If you're using SQL Server 2019 Express, you can download the installation media from Microsoft Download Center.

See also

OptimizeForTextSearch property
IsOptimizeForTextSearch method Table object Tables overview
Full-text search in SQL