Using SQL Server Full-Text Search in an ASP.NET Core + Angular Application

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;
}

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

Empty or short queries
Stopwords
SQL injection (parameters prevent this)
Pagination if results > 500 rows

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:

Redis
SQL Server In-Memory tables
Application caching using ASP.NET Core MemoryCache

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

Run tests with JMeter or k6
Measure latency under concurrent search operations

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.