A customer asks for documents about a term, and a leading wildcard query starts scanning every row. Full-text search basics help turn that vague request into a deliberate choice of search semantics.
Full-Text Search Basics Start With What Search Means
Before creating an index, ask what a match means. Does the user want a literal substring, a whole word, inflected forms, a phrase, a prefix term, or a ranked list? LIKE and full-text search answer different questions. LIKE can match a literal pattern inside a string, while a full-text predicate uses linguistic tokenization and full-text indexes. Neither is a universal replacement for the other.
I start with a handful of real search phrases and expected documents. That small example set exposes assumptions about punctuation, plural forms and stopwords. A search for a product code calls for exact string logic, while a search across long notes benefits from word-based indexing. If you cannot say which result should rank or match, an index choice alone will not fix the search experience.
Know the Catalog and Index Roles in Full-Text Search Basics
A full-text catalog is a logical container for full-text indexes. A full-text index tracks terms from selected character or supported document columns and maps them back to table rows. It requires a unique, single-column, nonnullable key index on the table. That key lets SQL Server identify the base row for a matching term. The full-text feature must also be installed and available on the instance.
I check the key and language before building the index. Language affects word breakers, stemming and stopword behavior, so a default that is wrong for the documents can give surprising matches. Population takes time, and newly changed rows are not necessarily searchable at the exact instant a test INSERT completes. A catalog is not a copy of the documents that you browse; it is infrastructure for finding terms.
SELECT name, is_default
FROM sys.fulltext_catalogs;
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
OBJECT_NAME(object_id) AS table_name,
is_enabled, change_tracking_state_desc
FROM sys.fulltext_indexes;Practice Full-Text Search Basics on a Small Table
In a disposable user database with Full-Text Search installed, create a document table and a unique key. Then create a catalog and full-text index on the body column. The script uses English language ID 1033 for the example; choose the language that matches your actual content. CREATE FULLTEXT INDEX names the unique key index rather than simply naming the key column.
I keep the base table and full-text schema changes in a reviewed deployment script. The full-text population state should be checked before using a new index for acceptance tests. The sample is small on purpose. A million-row document set raises the same questions: stable keys, chosen columns, language, population, and monitoring.
CREATE TABLE dbo.SearchDocumentDemo
(
document_id int NOT NULL,
body nvarchar(1000) NOT NULL,
CONSTRAINT PK_SearchDocumentDemo
PRIMARY KEY (document_id)
);
INSERT INTO dbo.SearchDocumentDemo (document_id, body)
VALUES
(1, N'SQL Server keeps transaction log records.'),
(2, N'A catalog stores terms for document search.');
CREATE FULLTEXT CATALOG SearchDocumentCatalog;
CREATE FULLTEXT INDEX ON dbo.SearchDocumentDemo
(
body LANGUAGE 1033
)
KEY INDEX PK_SearchDocumentDemo
ON SearchDocumentCatalog
WITH CHANGE_TRACKING AUTO;
Compare CONTAINS With LIKE
CONTAINS asks the full-text engine for term-based matches. LIKE applies a character pattern to the column. The first query can match an indexed term; the second looks for a literal substring with wildcards on both sides. A leading wildcard generally prevents a normal b-tree seek on that column. The returned rows can differ because word breaking, case behavior, punctuation and stopwords are not the same as substring matching.
I show both queries to the person who owns the search feature before declaring one correct. If the request is to find the exact sequence inside an identifier, LIKE or another exact matching strategy can be appropriate. If the request is to find meaningful words in prose, CONTAINS is a stronger fit. Search semantics are part of the product contract, not a private DBA preference.
SELECT document_id, body
FROM dbo.SearchDocumentDemo
WHERE CONTAINS(body, N'"transaction"');
SELECT document_id, body
FROM dbo.SearchDocumentDemo
WHERE body LIKE N'%transaction%';Understand Search Expressions
CONTAINS supports phrases, prefix terms and Boolean combinations, but the search condition has its own syntax. A phrase is not automatically the same as two independent words, and a prefix term requires the full-text prefix form. Build and test a small set of conditions against expected rows. Do not concatenate raw user input into a dynamic CONTAINS string without careful validation and parameter handling.
For ranking, CONTAINSTABLE can return a relevance rank that joins back to the base table. I use ranking only after checking that the basic matches are correct. A search result ordered by an unexplained score can look precise while returning the wrong category of document. A small relevance test set is more useful than arguing about whether a score of 62 feels scientific.
Watch Population and Operations
Full-text search basics include an operational lifecycle. Monitor catalog and index state after deployment, and distinguish an empty result caused by no matching term from one caused by an index that has not populated. Changes to the base table, language choice and stoplists affect what the search engine can return. Include full-text objects in backup, restore and environment validation plans that apply to the database.
I also check whether the text really belongs in SQL Server. Full-text search is practical when searchable documents live with relational data and queries need joins, filters and transactionally managed metadata. Specialized search services can be useful for broader ranking and distributed workloads, but they add another data pipeline. Choose the smallest system that satisfies the actual result quality and operational needs.
Choose the Tool by the Result
The choice fits in three lines. An exact identifier calls for equality. A short literal pattern can use LIKE when the table is small enough. Word and phrase search across a lot of text points to full-text search. Query plans, row counts and expected-result tests then confirm the choice. This is a better process than adding a full-text catalog because a query looked slow once.
I have seen teams confuse search speed with search correctness. A fast result that misses a document is still a bug, and a complete result delivered too slowly is still a user problem. Validate both. The most useful first full-text search test is not an impressive query; it is a small question whose expected answer everyone can agree on.
Related reading on this blog: Full-Text Search Not Working For PDF Documents and Always On Availability Groups and Full-Text Index.
Full-text search is not faster LIKE, it is a different way to ask for words and phrases.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
