Intelligent Enterprise Search: Building AI-Powered Search Using OpenAI Embeddings and SQL Server

Introduction

Enterprise data lives everywhere — in databases, documents, emails, and reports. Traditional keyword-based search often fails to understand meaning or context, leading to irrelevant results.

That’s where AI-powered search using OpenAI embeddings and SQL Server comes in. By combining vector similarity search with semantic understanding, you can build a search experience that understands intent, not just words.

In this educational article, we’ll learn — step by step — how to implement an intelligent semantic search engine using OpenAI embeddings, .NET 8 (ASP.NET Core), and SQL Server.

1. What Is AI-Powered Search?

Traditional search = keyword matching.
AI-powered search = semantic similarity.

Instead of matching exact words, embeddings represent text as vectors in multi-dimensional space — sentences with similar meanings have closer vector values.

For example

“Order shipment delayed” ≈ “Package not yet delivered”
Even though words differ, both phrases are semantically similar.

2. How Embeddings Work

Embeddings are numerical vector representations of text.
They allow mathematical comparison of meaning.

Example (simplified)

Text	Embedding (vector)
"Order delayed"	[0.12, 0.56, 0.33, ...]
"Shipment late"	[0.11, 0.57, 0.30, ...]

To find similarity, we calculate cosine similarity between vectors — a value close to 1 means similar meaning.

3. System Architecture Overview

Here’s the high-level architecture of our AI-Powered Search System:

        +-----------------------------------+
        |         Angular Frontend          |
        |  - Search Box                     |
        |  - Result Listing (Semantic)      |
        +-----------------+-----------------+
                          |
                          | HTTP Query
                          v
        +-----------------+-----------------+
        |       ASP.NET Core API            |
        |  - OpenAI Embedding Generator     |
        |  - SQL Server Query Handler       |
        +-----------------+-----------------+
                          |
                          | Store / Query
                          v
        +-----------------+-----------------+
        |      SQL Server Database          |
        |  - Text Data Table                |
        |  - Embedding Vector Columns       |
        +-----------------------------------+

4. Database Design for Semantic Search

We’ll store both text content and its vector embeddings.

CREATE TABLE Documents (
    Id INT IDENTITY PRIMARY KEY,
    Title NVARCHAR(200),
    Content NVARCHAR(MAX),
    Embedding VARBINARY(MAX)
);

Note: SQL Server 2022+ supports vector data type and vector indexes for AI workloads.

For older versions, you can store embeddings as VARBINARY(MAX) and process similarity in .NET.

5. Generating Embeddings with OpenAI

We’ll use the OpenAI Embeddings API (e.g., text-embedding-3-small) to generate vector representations.

Step 1: Add NuGet Packages

dotnet add package OpenAI

Step 2: Create a Service

using OpenAI.Embeddings;
using OpenAI;
using System.Text.Json;

public class EmbeddingService
{
    private readonly OpenAIClient _client;

    public EmbeddingService(IConfiguration config)
    {
        _client = new OpenAIClient(config["OpenAI:ApiKey"]);
    }

    public async Task<float[]> GenerateEmbeddingAsync(string text)
    {
        var response = await _client.EmbeddingsEndpoint.CreateEmbeddingAsync(
            model: "text-embedding-3-small",
            input: text
        );

        return response.Data[0].Embedding.ToArray();
    }
}

6. Storing Embeddings in SQL Server

Convert the float array to binary for storage:

public async Task SaveDocumentAsync(string title, string content)
{
    var embedding = await _embeddingService.GenerateEmbeddingAsync(content);
    var bytes = MemoryMarshal.AsBytes(embedding.AsSpan()).ToArray();

    using var connection = new SqlConnection(_connectionString);
    using var cmd = new SqlCommand("INSERT INTO Documents (Title, Content, Embedding) VALUES (@t, @c, @e)", connection);
    cmd.Parameters.AddWithValue("@t", title);
    cmd.Parameters.AddWithValue("@c", content);
    cmd.Parameters.AddWithValue("@e", bytes);
    connection.Open();
    await cmd.ExecuteNonQueryAsync();
}

7. Searching Using Embeddings

To perform a search:

Generate embedding for the query text.
Compare it against all stored embeddings using cosine similarity.

Cosine Similarity Formula

cosine_similarity = (A • B) / (||A|| * ||B||)

Sample .NET Code

private double CosineSimilarity(float[] a, float[] b)
{
    double dot = 0, magA = 0, magB = 0;
    for (int i = 0; i < a.Length; i++)
    {
        dot += a[i] * b[i];
        magA += a[i] * a[i];
        magB += b[i] * b[i];
    }
    return dot / (Math.Sqrt(magA) * Math.Sqrt(magB));
}

Then, query all documents and compute similarity:

public async Task<IEnumerable<SearchResult>> SearchAsync(string query)
{
    var queryEmbedding = await _embeddingService.GenerateEmbeddingAsync(query);
    var results = new List<SearchResult>();

    using var connection = new SqlConnection(_connectionString);
    using var cmd = new SqlCommand("SELECT Id, Title, Content, Embedding FROM Documents", connection);
    connection.Open();
    using var reader = await cmd.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        var embeddingBytes = (byte[])reader["Embedding"];
        var embedding = MemoryMarshal.Cast<byte, float>(embeddingBytes.AsSpan()).ToArray();

        double similarity = CosineSimilarity(queryEmbedding, embedding);
        results.Add(new SearchResult
        {
            Title = reader["Title"].ToString(),
            Content = reader["Content"].ToString(),
            Score = similarity
        });
    }

    return results.OrderByDescending(r => r.Score).Take(10);
}

8. Angular Frontend Integration

You can connect your Angular 17 app to the ASP.NET Core API easily.

search.component.ts

this.http.post<SearchResult[]>('/api/search', { query: this.searchText })
  .subscribe(results => {
    this.results = results;
  });

search.component.html

<div>
  <input [(ngModel)]="searchText" placeholder="Search anything..." />
  <button (click)="onSearch()">Search</button>

  <div *ngFor="let item of results" class="result">
    <h3>{{ item.title }}</h3>
    <p>{{ item.content }}</p>
    <small>Score: {{ item.score | number:'1.2-2' }}</small>
  </div>
</div>

9. Optimizing for Enterprise Scale

Strategy	Purpose
Vector Indexes (SQL Server 2022+)	Enables fast similarity search
Batch Embedding Generation	Reduces API calls and latency
Caching (Redis)	Cache repeated queries
Sharding Large Datasets	Improves performance for big corpora
Fine-Tuned Models	Use custom embeddings for domain-specific accuracy

10. Security and Cost Considerations

Store OpenAI API keys in Azure Key Vault or User Secrets.
Limit embedding generation per user with rate limiting middleware.
Cache embeddings to avoid repeated billing on the same text.

11. Use Cases

Scenario	Description
Knowledge Base Search	Query internal documents semantically
Ticket Classification	Find similar customer issues automatically
Contract Search	Match legal clauses across files
FAQ Matching	AI answers based on contextually relevant results

12. Example Workflow Summary

User Query → ASP.NET Core API → OpenAI Embedding → SQL Server Vector Search → Ranked Semantic Results → Angular UI

Conclusion

By combining OpenAI embeddings with SQL Server, you can transform traditional text search into a semantic, intent-aware engine.

This approach scales easily with ASP.NET Core’s API structure and integrates seamlessly with Angular or React frontends. Whether you’re building enterprise knowledge bases, customer support tools, or smart document search, this architecture lays the foundation for AI-powered discovery within your organization.