SQL Server Full-Text Search: an overview

Although we have ElasticSearch running in our environment, our most important search functionality is still handled by SQL Server. And no, it is not by writing a statement like WHERE Name LIKE '%term%'. That would probably work on a small table, but we have a lot of data to dig through, so we need something smarter than substring matching.

SQL Server has a built-in answer for this: Full-Text Search.

In this post I walk through the building blocks and the main features. In the next post I'll show what happened when a customer ran into a surprising side effect.

Why do we need this?

A LIKE with a leading wildcard can't use an index seek. SQL Server has to scan every row and compare strings. On top of that, LIKE knows nothing about language: searching for 'ride' won't find 'riding', and there is no concept of relevance.

Full-text search works differently. It builds an inverted index: a list of words (tokens) and, for every word, the rows where it occurs. A language-specific word breaker decides what a word is. A stemmer knows that 'ride', 'rides', 'rode' and 'riding' belong together.

Is it installed?

On-premises, Full-Text Search is an optional feature during setup. You can check if it's available with this query:

SELECT SERVERPROPERTY('IsFullTextInstalled') AS IsInstalled;

Remark: Azure SQL Database supports full-text search as well, nothing to install there.

The building blocks

A few concepts you need to know:

• Full-text catalog: a logical container for one or more full-text indexes.
• Full-text index: one per table, built over one or more columns. The columns can be text (char, varchar, nvarchar, xml) or binary (varbinary(max)) with documents like Word or PDF files.
• Unique key index: the full-text index needs a unique, single-column, non-nullable index on the table to map its entries back to rows. In most cases this is your primary key.
• Word breaker and stemmer: language-specific components that split text into words and find the different forms of a word.
• Stoplist: a list of words that are not indexed. More about this later.

Remark: If you like to go in-depth on the full text architecture, have a look at the official documentation: Full-Text Search - SQL Server | Microsoft Learn

Creating a full-text index

Let's use a simple product table as the example:

CREATE TABLE dbo.Products
(
    ProductId   int IDENTITY(1,1) NOT NULL,
    Name        nvarchar(200)     NOT NULL,
    Description nvarchar(max)     NULL,
    CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductId)
);

First create the catalog, then the index:

CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON dbo.Products
(
    Name        LANGUAGE 1033,
    Description LANGUAGE 1033
)
KEY INDEX PK_Products ON ProductCatalog
WITH CHANGE_TRACKING = AUTO;

The LANGUAGE 1033 is the locale identifier (LCID) for English. It decides which word breaker, stemmer, thesaurus and stopwords are used for that column. For Dutch you would use 1043.

The CHANGE_TRACKING option controls how the index is kept up to date:

• AUTO: changes are picked up automatically in the background.
• MANUAL: SQL Server tracks the changes but you decide when to apply them.
• OFF: nothing is tracked, you have to repopulate the index yourself.

Remark: the index is updated asynchronously. A row you just inserted can take a moment before it shows up in the search results.

Querying: CONTAINS

CONTAINS is the predicate you will use most. It supports a rich search syntax. A simple word search:

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Name, 'bike');

Prefix search

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Name, '"mount*"');

Remark: the double quotes are required. Without them the asterisk is treated as a normal character and you get no prefix matching.

Phrase search

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Description, '"carbon fiber frame"');

Boolean operators

Combine terms with AND, OR and AND NOT:

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Description, 'lightweight AND (aluminum OR carbon) AND NOT steel');

Proximity search

Find words that are close to each other with NEAR. Here at most 5 words apart:

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Description, 'NEAR((lightweight, frame), 5)');

Inflectional forms

This is where full-text search starts to beat LIKE. FORMSOF(INFLECTIONAL, ...) matches the other forms of a word:

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Description, 'FORMSOF(INFLECTIONAL, ride)');

This finds 'ride', 'rides', 'riding', 'rode' and 'ridden'.

Thesaurus

With FORMSOF(THESAURUS, ...) you can match synonyms:

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Description, 'FORMSOF(THESAURUS, bike)');

Remark: out of the box the thesaurus files are empty. You have to fill the XML file for your language (for English tsenu.xml) and load it with EXEC sys.sp_fulltext_load_thesaurus_file 1033;.

Querying: FREETEXT

FREETEXT is the less strict alternative. You give it a piece of natural language and SQL Server breaks it into words, looks at the different word forms and synonyms, and matches on meaning instead of on exact terms:

SELECT ProductId, Name
FROM dbo.Products
WHERE FREETEXT(Description, 'comfortable bike for long rides');

Simple to use, but you have less control over what is matched.

Ranking the results

CONTAINS and FREETEXT only tell you if a row matches. If you want to know how well it matches, use CONTAINSTABLE or FREETEXTTABLE. Both return a table with a KEY (the key of your row) and a RANK column:

SELECT p.ProductId, p.Name, ft.[RANK]
FROM CONTAINSTABLE(dbo.Products, (Name, Description),
     'ISABOUT(comfortable WEIGHT(.8), lightweight WEIGHT(.2))') AS ft
JOIN dbo.Products AS p ON p.ProductId = ft.[KEY]
ORDER BY ft.[RANK] DESC;

ISABOUT together with WEIGHT lets you tell SQL Server which terms matter most.

Language matters

The language you specify on the index is more important than it looks. It determines:

• how text is split into words
• which word forms belong together
• which thesaurus is used
• which words are considered stop words

You can also specify a language at query time:

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Description, 'fiets', LANGUAGE 1043);

Stop words

Full-text search doesn't index every word. Words that are considered too common to be useful, like 'the', 'and' or 'is', are stop words. They are listed in a stoplist and are skipped during indexing and querying.

The stoplist that is used by default is the system stoplist. You can look at what is in there for English:

SELECT stopword
FROM sys.fulltext_system_stopwords
WHERE language_id = 1033;

You have three options when creating or altering a full-text index:

• STOPLIST = SYSTEM: use the system stoplist (the default)
• STOPLIST = <name>: use your own custom stoplist
• STOPLIST = OFF: don't use a stoplist, so every word is indexed

A custom stoplist can be created from the system one, after which you can add or remove words:

CREATE FULLTEXT STOPLIST ProductStoplist FROM SYSTEM STOPLIST;

ALTER FULLTEXT STOPLIST ProductStoplist DROP 'both' LANGUAGE 1033;

ALTER FULLTEXT INDEX ON dbo.Products SET STOPLIST ProductStoplist;

Stop words sound like a harmless optimization. In the next post I'll explain why they are not always harmless.

Troubleshooting helpers

When search results are not what you expect, these are useful:

• sys.fulltext_indexes and sys.fulltext_catalogs: which indexes exist and how they are configured
• sys.dm_fts_parser: shows how the word breaker and stoplist process a query string
• sys.dm_fts_index_keywords: shows which words are actually in the index

-- How is this query string split up?
SELECT display_term, special_term
FROM sys.dm_fts_parser('"lightweight carbon frame"', 1033, 0, 0);

-- Which words are in the index?
SELECT TOP 20 display_term, document_count
FROM sys.dm_fts_index_keywords(DB_ID(), OBJECT_ID('dbo.Products'))
ORDER BY document_count DESC;

Wrapping up

Full-text search gives you fast, language-aware searching without adding another component to your architecture. That's it! Just keep an eye on those defaults, they can bite you.

More information

• Full-Text Search (SQL Server docs)
• Query with Full-Text Search