Vector databases are everywhere in AI discussions. But if you already have SQL Server, you might not need one. EF Core 10 brings native vector search to your existing data layer.
Common questions this answers
- What is vector search and when do I need it?
- How do I store embeddings in SQL Server with EF Core?
- How do I query for semantic similarity?
- When should I use SQL Server vectors vs a dedicated vector database?
- What are the performance limits of exact vs approximate search?
Definition (what this means in practice)
Vector search finds items by semantic similarity rather than exact matching. You convert text (or images, audio) into numeric vectors called embeddings using an AI model. Then you find the closest vectors to a query vector using distance metrics.
In practice, this powers semantic search (find documents about a concept, not just keywords), recommendation systems, and RAG (Retrieval Augmented Generation) where you retrieve relevant context before sending it to an LLM.
Terms used
- Embedding: a numeric vector representing the semantic meaning of content.
- Vector: an array of floating-point numbers (e.g., 1536 floats for OpenAI ada-002).
- Semantic search: finding content by meaning rather than keyword matching.
- Distance metric: how similarity is measured (cosine, euclidean, dot product).
- RAG: Retrieval Augmented Generation, adding retrieved context to LLM prompts.
- k-NN: k-nearest neighbors, exact search finding the closest vectors.
- ANN: approximate nearest neighbors, faster search with acceptable accuracy tradeoff.
Reader contract
This article is for:
- Engineers adding semantic search to .NET applications.
- Teams evaluating whether SQL Server vectors are sufficient for their AI workloads.
You will leave with:
- A decision framework for SQL Server vectors vs dedicated vector databases.
- Working code for storing and querying embeddings with EF Core 10.
- Performance guidelines for exact vs approximate search.
This is not for:
- Dedicated vector database deep-dives (Pinecone, Weaviate, Qdrant).
- Embedding model selection and training.
- Full RAG application architecture.
Quick start (10 minutes)
If you want to add semantic search to an existing SQL Server application:
Verified on: EF Core 10 (.NET 10), SQL Server 2025 / Azure SQL Database.
1. Define the entity with a vector property:
public class Article
{
public int Id { get; set; }
public string Title { get; set; } = string.Empty;
public string Content { get; set; } = string.Empty;
[Column(TypeName = "vector(1536)")]
public SqlVector<float>? Embedding { get; set; }
}
2. Generate and store embeddings:
// Using Microsoft.Extensions.AI
IEmbeddingGenerator<string, Embedding<float>> generator = /* your provider */;
var embedding = await generator.GenerateVectorAsync(article.Content);
article.Embedding = new SqlVector<float>(embedding);
await context.SaveChangesAsync();
3. Query by semantic similarity:
var queryVector = new SqlVector<float>(
await generator.GenerateVectorAsync("user search query")
);
var results = await context.Articles
.OrderBy(a => EF.Functions.VectorDistance("cosine", a.Embedding, queryVector))
.Take(10)
.ToListAsync();
When vector search makes sense
Use vector search when
- Users search by concept, not exact keywords ("articles about developer productivity" should find content about efficiency, workflow optimization, time management).
- You need to find similar items (related products, similar documents, recommendations).
- You are building RAG applications that retrieve context for LLM prompts.
- You want to keep embeddings alongside relational data in one database.
Skip vector search when
- Exact keyword matching is sufficient (product SKUs, error codes, names).
- Your dataset is small enough for full-text search to work well.
- You need sub-millisecond latency at massive scale (dedicated vector databases may be better).
- You are already using a vector database and happy with it.
Decision matrix
| Criterion | SQL Server Vectors | Dedicated Vector DB |
|---|---|---|
| Existing SQL Server infrastructure | Preferred | Adds complexity |
| Need ACID with relational data | Preferred | Requires sync |
| Fewer than 50,000 vectors | Sufficient (exact search) | Overkill |
| Millions of vectors | Consider (with indexing) | May be better |
| Team SQL Server expertise | Use existing skills | New technology |
| Pure vector workload only | Possible | Purpose-built |
SqlVector type configuration
EF Core 10 introduces SqlVector<float> for storing embeddings. The dimension must match your embedding model.
Data annotations
public class Document
{
public int Id { get; set; }
public string Content { get; set; } = string.Empty;
// OpenAI ada-002 produces 1536 dimensions
[Column(TypeName = "vector(1536)")]
public SqlVector<float>? Embedding { get; set; }
}
Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Document>()
.Property(d => d.Embedding)
.HasColumnType("vector(1536)");
}
Common embedding dimensions
| Model | Dimensions |
|---|---|
| OpenAI text-embedding-ada-002 | 1536 |
| OpenAI text-embedding-3-small | 1536 |
| OpenAI text-embedding-3-large | 3072 |
| Azure OpenAI embeddings | 1536 (default) |
| Sentence Transformers (varies) | 384-1024 |
Generating embeddings
Use Microsoft.Extensions.AI for a unified embedding interface across providers.
Setup with Azure OpenAI
// Program.cs
builder.Services.AddSingleton<IEmbeddingGenerator<string, Embedding<float>>>(sp =>
{
var client = new AzureOpenAIClient(
new Uri(builder.Configuration["AzureOpenAI:Endpoint"]!),
new DefaultAzureCredential());
return client.GetEmbeddingClient("text-embedding-ada-002")
.AsIEmbeddingGenerator();
});
Generating and storing
public class EmbeddingService(
AppDbContext db,
IEmbeddingGenerator<string, Embedding<float>> generator)
{
public async Task GenerateEmbeddingAsync(int documentId)
{
var document = await db.Documents.FindAsync(documentId);
if (document is null) return;
var embedding = await generator.GenerateVectorAsync(document.Content);
document.Embedding = new SqlVector<float>(embedding);
await db.SaveChangesAsync();
}
}
Querying with VECTOR_DISTANCE
EF.Functions.VectorDistance() translates to the SQL Server VECTOR_DISTANCE() function.
Basic similarity search
public async Task<List<Document>> SearchAsync(string query, int limit = 10)
{
var queryEmbedding = await generator.GenerateVectorAsync(query);
var queryVector = new SqlVector<float>(queryEmbedding);
return await db.Documents
.OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, queryVector))
.Take(limit)
.ToListAsync();
}
Distance metrics
| Metric | Use case | Range |
|---|---|---|
| cosine | Text similarity (most common) | 0 to 2 (0 = identical) |
| euclidean | Spatial distance | 0 to infinity |
| dot | When vectors are normalized | Varies |
Cosine distance is the default choice for text embeddings.
Including distance in results
var results = await db.Documents
.Select(d => new
{
Document = d,
Distance = EF.Functions.VectorDistance("cosine", d.Embedding, queryVector)
})
.OrderBy(x => x.Distance)
.Take(10)
.ToListAsync();
Filtering before vector search
Reduce the search space with relational filters first:
var results = await db.Documents
.Where(d => d.CategoryId == categoryId) // Filter first
.Where(d => d.CreatedAt > cutoffDate)
.OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, queryVector))
.Take(10)
.ToListAsync();
Performance considerations
Exact search limits
Exact search (k-NN) calculates distance against all vectors. Microsoft recommends exact search for datasets with fewer than 50,000 vectors after filtering.
// This is fine for small datasets
var results = await db.Documents
.OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, queryVector))
.Take(10)
.ToListAsync();
Approximate search (preview)
For larger datasets, SQL Server 2025 supports approximate search with DiskANN indexing via VECTOR_SEARCH(). However, this function is not yet supported in EF Core 10. Use raw SQL if needed:
// Raw SQL for approximate search (when supported)
var results = await db.Documents
.FromSqlRaw(@"
SELECT d.*
FROM VECTOR_SEARCH(
TABLE = Documents AS d,
COLUMN = Embedding,
SIMILAR_TO = {0},
METRIC = 'cosine',
TOP_N = 10
) AS s
ORDER BY s.distance", queryVector)
.ToListAsync();
Performance guidelines
| Vector count | Approach | Expected performance |
|---|---|---|
| Under 10,000 | Exact search | Fast (milliseconds) |
| 10,000 - 50,000 | Exact search with filters | Acceptable |
| 50,000+ | Consider approximate search | Evaluate latency requirements |
DiskANN indexing for approximate search
SQL Server 2025 introduces DiskANN-based vector indexes for approximate nearest neighbor (ANN) search. This trades perfect recall for dramatically better performance at scale.
-- Create a DiskANN index (SQL Server 2025+)
CREATE VECTOR INDEX IX_Documents_Embedding
ON Documents(Embedding)
WITH (
METRIC = 'cosine',
TYPE = DISKANN,
MAXDOP = 4
);
DiskANN characteristics:
| Property | Exact Search (k-NN) | Approximate Search (ANN) |
|---|---|---|
| Recall | 100% | 95-99% typical |
| Speed at 1M vectors | Seconds | Milliseconds |
| Index storage | None | Additional disk space |
| Build time | None | Minutes to hours |
| When to use | < 50K vectors | > 50K vectors |
Trade-off guidance:
- ANN recall of 95% means 5% of results might not be the absolute closest vectors
- For most applications (search, recommendations), this is acceptable
- For applications requiring exact results (deduplication), use exact search
Hybrid search: combining keyword and vector
Pure vector search can miss exact matches. Combine with full-text search for better results:
public async Task<List<SearchResult>> HybridSearchAsync(
string query,
int limit = 10)
{
var queryVector = new SqlVector<float>(
await generator.GenerateVectorAsync(query)
);
// Vector search results
var vectorResults = await db.Documents
.Where(d => d.Embedding != null)
.Select(d => new
{
Document = d,
VectorScore = 1 - EF.Functions.VectorDistance("cosine", d.Embedding, queryVector)
})
.OrderByDescending(x => x.VectorScore)
.Take(limit * 2)
.ToListAsync();
// Full-text search results (if configured)
var keywordResults = await db.Documents
.Where(d => EF.Functions.Contains(d.Content, query))
.Select(d => new { Document = d, KeywordScore = 1.0 })
.Take(limit * 2)
.ToListAsync();
// Combine and deduplicate with reciprocal rank fusion
return CombineResults(vectorResults, keywordResults, limit);
}
private List<SearchResult> CombineResults(/* ... */)
{
// Reciprocal Rank Fusion (RRF) scoring
// RRF(d) = sum(1 / (k + rank_i)) for each result list
const int k = 60; // RRF constant
var scores = new Dictionary<int, double>();
for (int i = 0; i < vectorResults.Count; i++)
{
var id = vectorResults[i].Document.Id;
scores[id] = scores.GetValueOrDefault(id) + 1.0 / (k + i + 1);
}
for (int i = 0; i < keywordResults.Count; i++)
{
var id = keywordResults[i].Document.Id;
scores[id] = scores.GetValueOrDefault(id) + 1.0 / (k + i + 1);
}
return scores
.OrderByDescending(kv => kv.Value)
.Take(limit)
.Select(kv => /* map to SearchResult */)
.ToList();
}
## Cost analysis
Understanding costs helps you budget for vector search workloads.
### Embedding generation costs
| Provider | Model | Cost per 1M tokens | Dimensions |
| --- | --- | --- | --- |
| OpenAI | text-embedding-3-small | $0.02 | 1536 |
| OpenAI | text-embedding-3-large | $0.13 | 3072 |
| OpenAI | text-embedding-ada-002 | $0.10 | 1536 |
| Azure OpenAI | Same models | Same pricing | Same |
**Estimation example:**
- 100,000 documents averaging 500 tokens each = 50M tokens
- Using text-embedding-3-small: 50 * $0.02 = $1.00 for initial indexing
- Re-indexing 1% daily: $0.01/day = $3.65/year
### Storage costs
Each vector dimension is 4 bytes (float32):
| Dimensions | Storage per vector | 100K vectors | 1M vectors |
| --- | --- | --- | --- |
| 384 | 1.5 KB | 150 MB | 1.5 GB |
| 1536 | 6 KB | 600 MB | 6 GB |
| 3072 | 12 KB | 1.2 GB | 12 GB |
**Azure SQL Database pricing context:**
- General Purpose: ~$0.115/GB/month storage
- 1M vectors at 1536 dimensions: 6 GB = ~$0.69/month storage
- Compute dominates cost; storage is negligible
### Compute costs: SQL Server vs dedicated vector DB
| Workload | SQL Server (existing) | New Vector DB |
| --- | --- | --- |
| Infrastructure | Shared with relational | Separate service |
| Operations | Existing team skills | New expertise needed |
| 50K vectors | Negligible additional cost | ~$50-200/month minimum |
| 1M vectors | May need higher tier | ~$200-500/month |
| 10M vectors | Dedicated instance likely | ~$500-2000/month |
**Key insight:** For < 100K vectors, SQL Server vector support often costs nothing extra if you have existing capacity. Dedicated vector databases make sense at scale or for pure vector workloads.
### Total cost comparison (100K documents)
| Approach | Year 1 | Ongoing/year |
| --- | --- | --- |
| SQL Server (existing) | ~$50 (embeddings) | ~$20 |
| SQL Server (new instance) | ~$1,200+ | ~$1,200+ |
| Pinecone Starter | ~$70 | ~$70 |
| Pinecone Standard | ~$840 | ~$840 |
| Self-hosted (Qdrant) | ~$600+ (compute) | ~$600+ |
## RAG pattern with EF Core
Retrieval Augmented Generation retrieves relevant context before prompting an LLM.
```csharp
public class RagService(
AppDbContext db,
IEmbeddingGenerator<string, Embedding<float>> embedder,
IChatClient chatClient)
{
public async Task<string> AnswerAsync(string question)
{
// 1. Find relevant documents
var queryVector = new SqlVector<float>(
await embedder.GenerateVectorAsync(question)
);
var relevantDocs = await db.Documents
.OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, queryVector))
.Take(3)
.Select(d => d.Content)
.ToListAsync();
// 2. Build context-augmented prompt
var context = string.Join("\n\n", relevantDocs);
var prompt = $"""
Answer the question based on the following context.
If the context does not contain relevant information, say so.
Context:
{context}
Question: {question}
""";
// 3. Get LLM response
var response = await chatClient.GetResponseAsync(prompt);
return response.Text;
}
}
Copy/paste artifact: vector search setup
// Entity with embedding
public class Document
{
public int Id { get; set; }
public string Title { get; set; } = string.Empty;
public string Content { get; set; } = string.Empty;
[Column(TypeName = "vector(1536)")]
public SqlVector<float>? Embedding { get; set; }
}
// Search service
public class SemanticSearchService(
AppDbContext db,
IEmbeddingGenerator<string, Embedding<float>> generator)
{
public async Task<List<Document>> SearchAsync(string query, int limit = 10)
{
var queryVector = new SqlVector<float>(
await generator.GenerateVectorAsync(query)
);
return await db.Documents
.Where(d => d.Embedding != null)
.OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, queryVector))
.Take(limit)
.ToListAsync();
}
public async Task IndexDocumentAsync(Document document)
{
var embedding = await generator.GenerateVectorAsync(document.Content);
document.Embedding = new SqlVector<float>(embedding);
await db.SaveChangesAsync();
}
}
Common failure modes
Dimension mismatch: vector(1536) in the database but embedding model produces 768 dimensions. Ensure column type matches your model.
Null embeddings in queries: forgetting to filter out documents without embeddings causes errors.
Exact search on large datasets: scanning millions of vectors is slow. Use filters to reduce the search space or consider approximate search.
Inconsistent embedding models: generating embeddings with different models produces incompatible vectors. Use the same model for indexing and querying.
Missing Microsoft.Data.SqlClient update: binary vector transport requires Microsoft.Data.SqlClient 6.1 or later.
Checklist
- SQL Server 2025 or Azure SQL Database available.
- EF Core 10 with SqlVector type configured.
- Embedding dimension matches model output.
- Microsoft.Data.SqlClient 6.1+ for binary transport.
- Embedding generation integrated (Microsoft.Extensions.AI).
- Null embedding handling in queries.
- Dataset size evaluated for exact vs approximate search.
FAQ
Do I need a separate vector database?
Not necessarily. If you have fewer than 50,000 vectors and already use SQL Server, built-in vector support simplifies your architecture. Evaluate based on scale, latency requirements, and operational complexity.
What about the EFCore.SqlServer.VectorSearch extension?
EF Core 10's native support replaces this extension. Remove it from your project when upgrading to EF Core 10.
Can I use approximate search with EF Core?
The VECTOR_SEARCH() function for approximate search is not yet supported in EF Core 10. Use raw SQL or wait for future EF Core updates.
How do I handle documents without embeddings?
Filter them out in queries: .Where(d => d.Embedding != null). Consider making the embedding property nullable and indexing documents asynchronously.
What embedding model should I use?
OpenAI's text-embedding-ada-002 or text-embedding-3-small are common choices. Azure OpenAI provides the same models with enterprise features. For on-premises, consider Sentence Transformers.
How do I update embeddings when content changes?
Regenerate the embedding when the source content changes. Consider a background job that processes updated documents.
What to do next
If you are considering vector search for your .NET application, start with SQL Server vectors if you already have SQL Server infrastructure. Evaluate your vector count: under 50,000 vectors after filtering makes exact search viable.
For more on EF Core patterns, read EF Core Performance Mistakes That Ship to Production.
If you want help implementing semantic search in your application, reach out via Contact.
References
- EF Core SQL Server Vector Search
- SQL Server Vector Search and Vector Index
- Vector data type (Transact-SQL)
- Using SqlVector with EF Core and Dapper
- Microsoft.Extensions.AI
- Learn how to generate embeddings (Azure OpenAI)
- Retrieval-augmented Generation (RAG) in Azure AI Search
- Azure SQL DB Vector Search Samples
Author notes
Decisions:
- Focus on SQL Server vectors rather than dedicated vector databases. Rationale: simplifies architecture for teams already using SQL Server; dedicated databases are well-documented elsewhere.
- Emphasize exact search limits (50,000 vectors). Rationale: Microsoft's published guidance; prevents performance surprises.
- Note approximate search limitations in EF Core 10. Rationale: transparency about current capabilities; raw SQL is available as workaround.
Observations:
- Teams often add a separate vector database, then realize SQL Server vectors would have been sufficient for their scale.
- Dimension mismatches between stored vectors and query vectors are a common debugging session.
- Background embedding generation is essential for good UX; synchronous embedding on save is too slow.