Full-Text Search in Azure SQL

Full-text search in Azure SQL provides linguistic search capabilities against character-based data stored in SQL tables. Unlike the LIKE operator, which performs simple pattern matching and cannot index individual words efficiently, full-text search builds an inverted index that maps each significant word (token) to its locations within the indexed columns. This enables fast, flexible queries that can match words, phrases, inflectional forms (e.g., 'run', 'ran', 'running'), synonyms, and proximity (words near each other).

Full-text search is essential for applications that require search functionality similar to web search engines, such as e-commerce product search, document management, and content management systems. The DP-900 exam tests your ability to distinguish between basic pattern matching and full-text search, and to identify scenarios where full-text search is appropriate.

The full-text search process involves several components working together:

1. Full-Text Index Population: When you create a full-text index on a table, the Full-Text Engine (FTE) reads the data from the specified columns. For each row, it extracts the text and passes it to a word breaker (which identifies word boundaries) and a stemmer (which reduces words to their root forms). The resulting tokens are stored in an inverted index. This process is called a population. There are three types: - Full population: Indexes all rows. Occurs when the index is first created or rebuilt. - Incremental population: Indexes only rows that changed since the last population. Requires a timestamp column (timestamp or rowversion). - Manual population: Triggered by ALTER FULLTEXT INDEX START UPDATE POPULATION.

To create a full-text catalog:

CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;

To create a full-text index on a table Products with a primary key ProductID and columns Name and Description:

CREATE FULLTEXT INDEX ON Products
(
    Name LANGUAGE 1033,  -- English
    Description LANGUAGE 1033
)
KEY INDEX PK_Products
ON ProductCatalog
WITH (CHANGE_TRACKING AUTO);

The CHANGE_TRACKING AUTO option enables automatic incremental population. Other options: MANUAL (manual population) and OFF (no tracking).

To view full-text catalogs:

SELECT * FROM sys.fulltext_catalogs;

To view full-text indexes:

SELECT * FROM sys.fulltext_indexes;

To populate a full-text index manually:

ALTER FULLTEXT INDEX ON Products START UPDATE POPULATION;

Full-text search integrates with: - Azure SQL Database: Fully supported in single databases and elastic pools. No need for separate service. - Azure SQL Managed Instance: Same support as SQL Server. - Azure Synapse Analytics: Not supported for dedicated SQL pools; use LIKE or other text search methods. - Semantic Search: An extension of full-text search that extracts key phrases and document similarity. Not commonly tested on DP-900.

Full-text search is often compared to LIKE queries. The exam expects you to know that LIKE '%keyword%' cannot use an index efficiently and performs a table scan, while full-text search uses an index for fast retrieval. However, full-text search has overhead in index maintenance and storage, so it's not suitable for every scenario.

Common wrong answers on the exam:

To verify full-text index population status:

SELECT * FROM sys.dm_fts_index_population;

To search for a word using CONTAINS:

SELECT ProductID, Name
FROM Products
WHERE CONTAINS(Name, 'bike');

To search for meaning using FREETEXT:

SELECT ProductID, Name
FROM Products
WHERE FREETEXT(Name, 'mountain bicycle');

Both CONTAINS and FREETEXT can be used in a SELECT statement with a FROM clause. Note that CONTAINSTABLE and FREETEXTTABLE return a rank column, but these are more advanced.

1. E-commerce Product Search

An online retailer stores product names and descriptions in an Azure SQL Database. Customers need to search for products using natural language queries like 'blue running shoes'. Using full-text search with FREETEXT on the Description column allows the application to return relevant results even if the exact phrase isn't present. The full-text index is created with CHANGE_TRACKING AUTO so that new products are searchable immediately after insertion. The database is scaled to handle thousands of queries per second. A common misconfiguration is not using a stoplist, causing common words like 'the' and 'and' to bloat the index. In production, the team monitors the full-text index size and schedules a weekly rebuild during low traffic to defragment the index.

2. Document Management System

A law firm stores legal documents as varbinary(max) in Azure SQL Managed Instance. They use full-text search with filters (e.g., PDF filter) to extract text from the documents. Users can search for specific clauses or terms across thousands of documents. The full-text index includes a custom thesaurus to handle legal synonyms. The firm uses CONTAINS with proximity terms to find words within a certain distance (e.g., NEAR((breach, contract), 10)). A pitfall is that the filter daemon process can be resource-intensive; they had to increase the ft_ftdi_host_cpu setting (though not configurable in Azure SQL DB) and ensure the database has enough DTUs/vCores. Without proper indexing, searches would take minutes; with full-text, they take seconds.

3. Content Management for a News Portal

A news website uses Azure SQL Database to store articles. Editors need to search for articles by topic, author, or keyword. Full-text search enables ranking of results based on word frequency and proximity. They use CONTAINSTABLE to get ranked results. The full-text index is on the Title and Body columns. They also use language-specific word breakers for multilingual content. A common issue is that the full-text index does not automatically update for bulk inserts; they had to use CHANGE_TRACKING MANUAL and schedule population after nightly data loads. Without correct change tracking, the index becomes stale, and searches miss recent articles.

What Goes Wrong When Misconfigured