SQL Server Full-Text Search: Build a Useful Search Contract

A search box that runs LIKE '%term%' over a large article body often becomes expensive as the collection grows. SQL Server Full-Text Search offers a different access method, but it also changes the meaning of a match. It indexes linguistic tokens rather than every possible character substring. Replacing LIKE without agreeing on that difference can make search faster and less useful at the same time.

Separate the product's search needs. An exact product code, a word inside prose, a phrase, and an arbitrary substring are four different questions. An ordinary indexed equality lookup may be best for the code, while full-text search fits words and language-aware queries. Do not force every search field through one mechanism.

Create an index with an explicit language

This example creates permanent practice objects, so use a disposable database with Full-Text Search installed and sufficient setup permissions.

-- Run only in a disposable database with Full-Text Search installed.
CREATE TABLE dbo.SearchArticleDemo (
    ArticleId int NOT NULL CONSTRAINT PK_SearchArticleDemo PRIMARY KEY,
    Title nvarchar(200) NOT NULL,
    Body nvarchar(max) NOT NULL
);
INSERT dbo.SearchArticleDemo VALUES
(1, N'Index design', N'An index can reduce reads for selective queries.'),
(2, N'Backup planning', N'Restore tests verify that backups are usable.'),
(3, N'Query performance', N'Query performance depends on access paths and data.');
CREATE FULLTEXT CATALOG SearchArticleDemoCatalog;
CREATE FULLTEXT INDEX ON dbo.SearchArticleDemo
    (Title LANGUAGE 1033, Body LANGUAGE 1033)
KEY INDEX PK_SearchArticleDemo
ON SearchArticleDemoCatalog
WITH CHANGE_TRACKING AUTO;

The full-text key uses a unique, non-null identifier. Both text columns are indexed with English language rules, identified by LCID 1033. Language affects word breaking and inflection handling; it is not merely a label for the user interface.

A multilingual application should decide how each indexed column's language relates to the documents it contains. Placing unrelated languages under one arbitrary language setting can reduce relevance. Separate localized storage or a dedicated multilingual search service may be more suitable when the required behavior exceeds this model.

CHANGE_TRACKING AUTO maintains changes asynchronously. Creating the index does not mean the initial population is already complete. Likewise, a committed article edit may take time to appear in search. A zero-result query immediately after setup is therefore not sufficient evidence that the search expression is wrong.

Return ranked results with predictable ordering

FREETEXTTABLE accepts natural-language search text and returns matching keys with ranks.

DECLARE @Search nvarchar(4000) = N'index performance';
SELECT TOP (10) a.ArticleId, a.Title, ft.[RANK]
FROM FREETEXTTABLE(
    dbo.SearchArticleDemo, (Title, Body), @Search, LANGUAGE 1033
) AS ft
JOIN dbo.SearchArticleDemo AS a ON a.ArticleId = ft.[KEY]
ORDER BY ft.[RANK] DESC, a.ArticleId;

The precise rank values and order are properties of the indexed content and query; they are not a calibrated probability of relevance. ArticleId provides a stable tie-breaker for equal ranks. The English input here is intentional because the practice documents are English, even when the surrounding explanation is translated.

FREETEXT-style matching is useful when users type ordinary words. CONTAINS and CONTAINSTABLE expose a more structured search grammar for phrases, prefix terms, and Boolean combinations. A prefix term matches token prefixes, not arbitrary fragments in the middle of every token. Punctuation and word breakers can also make a technical identifier behave differently from a plain substring.

Pass search input as a SQL parameter. For structured full-text syntax, parameterization prevents SQL text concatenation but does not make every user string valid search grammar. Validate or construct the supported grammar separately and return a helpful message for invalid input.

Stoplists can remove common words. Test a query containing only stopwords, quoted phrases, hyphenated identifiers, and language-specific forms. Decide what an empty or unsearchable query should do rather than turning it into an unrestricted table scan.

Validate freshness, filtering, and relevance

This inspection helps distinguish missing installation support from population activity.

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS Installed,
       OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SearchArticleDemo'),
           'TableFulltextPopulateStatus') AS PopulationStatus;

Inspect population status and relevant full-text diagnostics when results are missing. An idle status alone does not prove every expected document was indexed correctly. Compare specific recently inserted identifiers and their searchable words, and investigate failed document processing where applicable.

Apply authorization and tenant restrictions before returning results. A common trap is requesting a small global top-ranked candidate set and filtering by tenant afterward: the tenant can receive too few rows even when it has many valid matches. Compare plans and completeness for the actual filtering strategy.

Test with representative content and a curated set of queries whose useful results are known. Measure latency and reads, but also inspect missed documents and misleading matches. A search feature can meet a response-time target while failing the user's task.

Finally, document the freshness promise and query behavior in product terms. Users should know whether they are searching exact codes or article language and whether a newly saved document may need a moment to appear. Useful search comes from a clear contract, appropriate indexing, and relevance checks together.

Technical references: Microsoft Learn: Full-text search · Microsoft Learn: FREETEXTTABLE · Microsoft Learn: CONTAINS.