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
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.