Searching large volumes of text efficiently is a common requirement in enterprise applications. Basic SQL LIKE queries work for simple filtering, but they perform poorly as data grows, especially when you need ranking, relevance, inflectional forms, synonyms, and phrase matching.
SQL Server Full-Text Search (FTS) provides a powerful, scalable search engine built directly into SQL Server. When integrated with an ASP.NET Core backend and an Angular frontend, it enables fast, relevance-based search for product catalogs, knowledge bases, support tickets, documents, blogs, and more.
This article provides a production-ready architecture and implementation blueprint, covering:
What Full-Text Search is and when to use it
Enabling FTS in SQL Server
Creating full-text catalogs and indexes
Writing CONTAINS, FREETEXT, and ranking queries
Implementing search in ASP.NET Core APIs
Angular UI and service integration
Performance, scaling, and real-world best practices
Security considerations
Testing and validation strategies
The article assumes experience with SQL Server, .NET, and Angular, and is aimed at senior developers and architects designing scalable search features.
1. Why SQL Server Full-Text Search?
Full-Text Search is designed for linguistic-based searching. Compared to normal LIKE operations, FTS is:
Much faster for large text columns
Able to understand language rules (stemming, inflectional forms)
Able to search phrases and combinations of words
Able to rank results by relevance
Able to use thesaurus files for synonyms
Efficient even with millions of rows
When to Use FTS
Use Full-Text Search when your application requires:
Searching multi-paragraph descriptions
Matching phrases (for example “wireless mouse”)
Relevance-based ranking
Searching multiple text columns
Handling plurals, variations, or tenses
Searching document content (PDF, Word, etc.)
Do not use Full-Text Search for:
Exact match lookups
Simple filtering on small datasets
Filtering purely on structured numeric fields (price, quantity)
2. Enabling SQL Server Full-Text Search
Most SQL Server installations already include FTS. You can verify it using:
SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS IsInstalled;
If it returns 1, FTS is available. On-prem SQL Express needs SQL Express with Advanced Services to support FTS.
3. Creating Full-Text Catalogs and Indexes
Suppose we have a Products table used in an e-commerce application:
CREATE TABLE Products (
ProductID INT PRIMARY KEY IDENTITY,
Name NVARCHAR(200),
Description NVARCHAR(MAX),
Category NVARCHAR(100),
Brand NVARCHAR(100)
);
3.1 Create a Full-Text Catalog
CREATE FULLTEXT CATALOG ProductCatalog;
3.2 Create a Full-Text Index
CREATE FULLTEXT INDEX ON Products
(
Name LANGUAGE 1033,
Description LANGUAGE 1033,
Category LANGUAGE 1033,
Brand LANGUAGE 1033
)
KEY INDEX PK__Products__ProductID
ON ProductCatalog;
Explanation
LANGUAGE 1033 = English
Full-Text Index can include multiple text columns
The key index must be a unique, non-nullable index
3.3 Populate the Index
Full-Text Index automatically populates, but you can force a rebuild:
ALTER FULLTEXT INDEX ON Products START FULL POPULATION;
4. Writing Full-Text Search Queries
4.1 CONTAINS Example
SELECT ProductID, Name, Description
FROM Products
WHERE CONTAINS((Name, Description), ' "wireless mouse" ');
Advantages
Phrase search
Uses linguistic variants
4.2 FREETEXT Example
SELECT ProductID, Name, Description
FROM Products
WHERE FREETEXT((Name, Description), 'comfortable ergonomic chair');
FREETEXT automatically finds related words.
4.3 Ranking Results Using FREETEXTTABLE
SELECT TOP 20
ft.[RANK] AS Score,
p.ProductID,
p.Name,
p.Description
FROM FREETEXTTABLE(Products, (Name, Description), 'gaming laptop') AS ft
JOIN Products p ON p.ProductID = ft.[KEY]
ORDER BY ft.[RANK] DESC;
4.4 Combine FTS With Filters
SELECT ft.[RANK] AS Score, p.*
FROM CONTAINSTABLE(Products, (Name, Description), ' "office chair" ') ft
JOIN Products p ON p.ProductID = ft.[KEY]
WHERE p.Category = 'Furniture'
ORDER BY ft.[RANK] DESC;
Production systems often combine FTS with filters like category, price range, or availability.
5. ASP.NET Core Backend Implementation
5.1 DTO for Search Requests
public class ProductSearchRequest
{
public string Query { get; set; }
public string Category { get; set; }
public int? MaxResults { get; set; } = 20;
}

Join the conversation! Your thoughts help the community grow.