Lesson 1

Another way that you can query your database is through SQL Server full-text searching, which lets you run linguistically aware queries against character-based data rather than plain pattern matching. After completing this module, you will be able to:

1. Describe what full-text searching is and when to use it.
2. Describe considerations for full-text searching.
3. Practice implementing full-text searching.

In the next lesson, you will get an overview of how full-text searching works.

Full-Text Search (FTS) is a structured word and phrase indexing system built into SQL Server. It goes well beyond matching literal characters: it performs linguistic analysis to determine a word's base form, recognize word boundaries, and conjugate verbs correctly across supported languages, all backed by a dedicated full-text index rather than a simple character scan. A plain keyword index can tell you that a string exists somewhere in a column; Full-Text Search can tell you that a document is actually about that concept, even when the exact word typed does not appear in it in that exact form.

More than just a word parser, Full-Text Search performs linguistic analysis by determining base words and word boundaries, and by conjugating verbs for different languages, which puts it well ahead of a simple word-index system built by hand. ANSI standard SQL uses the LIKE operator to perform basic word searches and even wildcard searches. For example, the following code uses LIKE to query the Aesop's Fables sample database:

USE Aesop;
SELECT Title
FROM Fable
WHERE Fabletext LIKE '%Lion%'
  AND Fabletext LIKE '%bold%';

Running this query returns a single row:

Title

The Hunter and the Woodman

The main problem with WHERE ... LIKE searches like this one is performance. An index is searchable from the beginning of a string, so LIKE 'word%' is fast, but LIKE '%word%' is terribly slow: SQL Server has no way to know in advance where inside the column the match will start, so it cannot use the b-tree structure of an index to perform a seek and instead falls back to a table scan. It is the same problem as looking for everyone named "Paul" in a phone book sorted by last name: the book is not indexed by first name, so every page has to be checked by hand.

Once a full-text index exists on the Fabletext column, the same kind of search can be rewritten using CONTAINS instead of LIKE:

-- ANSI SQL LIKE: forces a table scan on large text columns
SELECT Title, Fabletext
FROM Fable
WHERE Fabletext LIKE '%wolf%';

-- Full-Text Search: uses the full-text index instead
SELECT Title, Fabletext
FROM Fable
WHERE CONTAINS(Fabletext, 'wolf');

The CONTAINS version uses the full-text index rather than scanning every row, and is typically much faster and more accurate once the text column holds any real volume of content. Full-Text Search is a linguistically intelligent system: it handles stemming, stopwords, ranking, and multiple languages, all of which LIKE has no concept of at all.

All of the standard Full-Text Search predicates and rowset-valued functions described above are available once the Full-Text and Semantic Extractions for Search optional component is installed:

• CONTAINS / CONTAINSTABLE: precise or fuzzy matches on single words and phrases, prefix searches such as "optimiz*", proximity terms with NEAR, and inflectional or thesaurus forms through FORMSOF(INFLECTIONAL, ...).
• FREETEXT / FREETEXTTABLE: breaks a search string into individual words, generates inflectional forms, and matches by meaning rather than exact wording, returning relevance RANK scores when used as FREETEXTTABLE.

CONTAINSTABLE is especially useful when you want to rank results rather than just filter them:

SELECT
    p.ProductID,
    p.ProductName,
    p.ProductDescription,
    ft.[RANK]
FROM Production.Products AS p
INNER JOIN CONTAINSTABLE(
    Production.Products,
    ProductDescription,
    'NEAR((SSD, NVMe), 5) OR FORMSOF(INFLECTIONAL, "encrypt")'
) AS ft ON p.ProductID = ft.[KEY]
ORDER BY ft.[RANK] DESC;

This single query finds product descriptions where "SSD" and "NVMe" appear within five words of each other, or where any inflectional form of "encrypt" appears (encrypted, encrypting, encryption), and orders the results by how well each row matches.

SQL Server 2025 modernizes the word breaker and filter components behind Full-Text Search. New or rebuilt full-text indexes now default to index_version = 2, controlled by the FULLTEXT_INDEX_VERSION database-scoped configuration option, which expands support for additional languages and document formats.

This matters most during an upgrade. If a database is upgraded in place from SQL Server 2022 or earlier, its existing full-text indexes keep index_version = 1, and the version-1 filter and word-breaker components are not included in a SQL Server 2025 installation by default; they would need to be copied over manually from an older instance to keep using them. The more direct path is to rebuild the catalog so its indexes move to version 2:

-- Check the database-scoped configuration for new full-text indexes (default is 2)
SELECT [name], [value]
FROM sys.database_scoped_configurations
WHERE [name] = 'FULLTEXT_INDEX_VERSION';

-- Check which version your existing full-text indexes are using
SELECT
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) AS TableName,
    index_version
FROM sys.fulltext_indexes;

-- Move a catalog's indexes to version 2 (requires FULLTEXT_INDEX_VERSION = 2)
ALTER FULLTEXT CATALOG [YourCatalogName] REBUILD;

Full-Text Search remains the right engine for linguistic keyword indexing, but SQL Server 2025 adds two more ways to search text, and it is worth knowing where each one fits before you reach for Full-Text Search by habit:

The rest of this module focuses on Full-Text Search itself: how it is set up, how its catalogs and indexes behave, and how to write queries against it. Vector search and native regular expressions are both genuinely useful tools in SQL Server 2025, but they solve different problems and are covered elsewhere in this course rather than in this module.

In the next lesson, you will get an overview of how full-text searching actually works under the hood.