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:
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
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;
}
5.2 Dapper or EF Core?
You can use either, but Dapper is preferred for performance-sensitive search endpoints.
However, EF Core also works using raw SQL queries.
5.3 Repository Using Dapper
public async Task<IEnumerable<ProductSearchResult>> SearchProducts(ProductSearchRequest req)
{
using var connection = new SqlConnection(_connectionString);
var sql = @"
SELECT TOP (@max)
ft.[RANK] AS Score,
p.ProductID,
p.Name,
p.Description,
p.Category
FROM FREETEXTTABLE(Products, (Name, Description, Category, Brand), @query) ft
JOIN Products p ON p.ProductID = ft.[KEY]
WHERE (@category IS NULL OR p.Category = @category)
ORDER BY ft.[RANK] DESC";
return await connection.QueryAsync<ProductSearchResult>(sql,
new { query = req.Query, category = req.Category, max = req.MaxResults });
}
5.4 API Controller
[ApiController]
[Route("api/[controller]")]
public class SearchController : ControllerBase
{
private readonly IProductRepository _repo;
public SearchController(IProductRepository repo)
{
_repo = repo;
}
[HttpPost]
public async Task<IActionResult> Search(ProductSearchRequest request)
{
if (string.IsNullOrWhiteSpace(request.Query))
return BadRequest("Query cannot be empty.");
var results = await _repo.SearchProducts(request);
return Ok(results);
}
}
5.5 Handle Edge Cases
6. Angular Frontend Integration
6.1 Angular Service
@Injectable({ providedIn: 'root' })
export class SearchService {
private apiUrl = '/api/search';
constructor(private http: HttpClient) {}
search(query: string, category?: string) {
return this.http.post<ProductSearchResult[]>(this.apiUrl, {
query,
category,
maxResults: 20
});
}
}
6.2 Angular Component
export class ProductSearchComponent {
searchTerm = '';
category = '';
results: ProductSearchResult[] = [];
loading = false;
constructor(private searchService: SearchService) {}
performSearch() {
if (!this.searchTerm.trim()) return;
this.loading = true;
this.searchService.search(this.searchTerm, this.category)
.subscribe({
next: res => {
this.results = res;
this.loading = false;
},
error: () => this.loading = false
});
}
}
6.3 Template Example
<input [(ngModel)]="searchTerm" placeholder="Search products" />
<select [(ngModel)]="category">
<option value="">All</option>
<option value="Furniture">Furniture</option>
<option value="Electronics">Electronics</option>
</select>
<button (click)="performSearch()">Search</button>
<div *ngIf="loading">Searching...</div>
<ul *ngIf="results.length">
<li *ngFor="let r of results">
<strong>{{ r.name }}</strong> (Score: {{ r.score }})
<p>{{ r.description }}</p>
</li>
</ul>
6.4 Debouncing User Input (Best Practice)
Use RxJS debounceTime for real-time search:
searchControl.valueChanges
.pipe(debounceTime(400), distinctUntilChanged())
.subscribe(value => this.performSearch());
7. Performance and Scaling Best Practices
7.1 Index Optimization
Add only necessary text columns to FTS index
Use appropriate LANGUAGE settings
Rebuild catalog periodically for large updates
7.2 Query Optimization
Prefer FREETEXTTABLE or CONTAINSTABLE over FREETEXT when ranking is required
Use TOP with predictable limits
Combine FTS results with normal filters via JOIN rather than subqueries
7.3 Scaling
Full-Text Search benefits from CPU; scale SQL Server accordingly
Use SQL Server Enterprise features like stoplists and thesaurus
For extremely high-scale applications, consider hybrid caching (Redis + FTS)
7.4 Caching
Cache results for repeated popular searches:
8. Security Considerations
Use parameterized queries to prevent SQL injection
Do not expose raw SQL errors to frontend
Limit search terms to controlled length (for example, 200 characters)
Log search queries for auditing
Prevent heavy or repeated calls that could overload SQL Server
9. Testing and Validation
9.1 Unit Tests
Test repository logic using in-memory database or local SQL
Test API receives and returns valid DTOs
Validate query handling for edge cases like blank input
9.2 Integration Tests
Use test SQL Server with FTS enabled
Seed known data and test ranking consistency
Test performance thresholds on typical datasets
9.3 Load Testing
10. Summary
SQL Server Full-Text Search provides a robust, scalable, and enterprise-ready search engine that integrates smoothly with ASP.NET Core and Angular.
Key takeaways:
Create full-text catalogs and indexes for text-heavy tables
Write optimized FTS queries for relevance ranking
Build clean ASP.NET Core APIs for search
Integrate with Angular using services and debounced UI components
Apply performance optimization such as caching and index tuning
Secure search endpoints and validate input
Test ranking, performance, and scalability thoroughly
This architecture supports search for millions of records with low latency, making it ideal for e-commerce, content platforms, support ticketing systems, enterprise knowledge bases, and any system where search is a core capability.