Semantic Index with SQL Server & Azure SQL

Overview

SQL Server provides comprehensive vector storage capabilities for Semantic Kernel applications. SQL Server 2025 Preview introduces native vector support with the VECTOR data type, DiskANN-based approximate nearest neighbor (ANN) indexing, and built-in vector search functions - bringing enterprise-grade vector capabilities directly into your database.

For earlier versions (SQL Server 2019-2022), vector storage is available through VARBINARY or JSON approaches, making SQL Server a reliable, enterprise-ready solution for storing embeddings alongside your relational data.

Vector Store Capabilities

New to vector search? Check out Vector Search Basics for a primer on cosine similarity, ANN indexes (HNSW, IVF), and how approximate nearest neighbor algorithms work.

SQL Server 2025 Preview - Native Vector Support

NEW in SQL Server 2025 (17.x) Preview:

SQL Server 2025 introduces first-class vector support designed for AI and RAG applications:

Native VECTOR Data Type
- Store vectors efficiently with built-in data type: VECTOR(dimensions)
- Support for both float32 (default, up to 1,998 dimensions) and float16 (up to 3,996 dimensions)
- Optimized binary storage format, exposed as JSON arrays for convenience
- Direct conversion between JSON arrays and VECTOR type
DiskANN Vector Indexing
- CREATE VECTOR INDEX using Microsoft Research’s DiskANN algorithm
- Graph-based indexing for fast approximate nearest neighbor (ANN) search (similar to HNSW but optimized for disk)
- Efficiently uses SSD storage with minimal memory footprint
- High queries per second (QPS) with low latency
- Maintains excellent recall while balancing memory, CPU, and I/O
Built-in Vector Functions
- VECTOR_DISTANCE - Exact k-NN search with cosine, euclidean, or dot product metrics
- VECTOR_SEARCH - Approximate nearest neighbor search using vector indexes
- Native distance calculations - no custom UDFs required

Preview Feature Activation:

-- Required to enable vector features in SQL Server 2025 Preview
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
GO

When to Use Each Approach

Choose Exact Search (VECTOR_DISTANCE) for:

Small datasets (< 50,000 vectors)
When accuracy is critical (100% recall required)
Filtered searches where predicates reduce result set to < 50,000 vectors
Development and testing scenarios
Precision-critical applications (e.g., scientific research, sensitive recommendations)

Choose ANN Search (VECTOR_SEARCH with DiskANN index) for:

Large datasets (> 50,000 vectors)
Performance-critical applications requiring low latency
High-throughput scenarios with thousands of queries per second
Production RAG applications with large knowledge bases
When ~95-99% recall is acceptable (typical for AI/semantic search - see Why ANN Indexes?)

SQL Server 2019-2022 - Storage Approaches

For versions without native vector support:

VARBINARY Storage - Store vectors as binary data (recommended)
JSON/NVARCHAR Storage - Store vectors as JSON arrays (less efficient)

Vector search in these versions requires:

Brute Force Search - Calculate similarity for all vectors (custom T-SQL/CLR)
Pre-filtering - Use WHERE clauses to reduce search space
Partitioning - Distribute vectors for better performance

SQL Server Variants Comparison

SQL Server 2025 Preview (On-Premise & Azure)

Native Vector Features:

VECTOR data type (float32/float16)
CREATE VECTOR INDEX with DiskANN algorithm
VECTOR_DISTANCE for exact k-NN search
VECTOR_SEARCH for approximate NN search
Support for cosine, euclidean, and dot product distance metrics

Capabilities:

Production-ready vector search for datasets up to billions of vectors
Sub-100ms query latency with proper indexing
High recall (95-99%) with ANN indexes
Perfect recall (100%) with exact search
Store vectors alongside relational data with ACID guarantees

Supported Platforms:

SQL Server 2025 (17.x) Preview on Windows/Linux
Azure SQL Managed Instance (SQL Server 2025 or Always-up-to-date policy)
Azure SQL Database (preview support)
SQL database in Microsoft Fabric (preview)

Performance Characteristics:

Small datasets (< 50k): Exact search recommended, sub-second queries
Large datasets (> 50k): DiskANN index required, sub-100ms queries at scale
Very large datasets (> 1M): Excellent performance with proper index tuning

On-Premise SQL Server 2019-2022

Supported Versions:

SQL Server 2019 and later (for JSON functions)
SQL Server 2022+ recommended (better JSON performance)

Capabilities:

Vector storage via VARBINARY or JSON
Full control over indexing strategies
Custom UDFs for similarity calculations
No size limitations beyond hardware

Limitations:

No native vector indexing
Performance depends on hardware
Manual scaling required
Similarity search is O(n) - scans all vectors
You manage all security and backups

Azure SQL Database (Managed)

SQL Server 2025 Preview on Azure SQL:

All native vector features available
Automatic backups and high availability
Built-in security features (Always Encrypted, TDE)
Elastic pools for cost optimization
Geo-replication support

Service Tiers (with Vector Support):

Basic/Standard - Development and small datasets (< 50k vectors)
Premium - Production workloads with exact search or small ANN datasets
Business Critical - High-performance scenarios with large vector indexes
Hyperscale - Massive datasets (> 10M vectors) with read replicas

Limitations:

Preview features require PREVIEW_FEATURES database configuration
Cost increases with scale and index size
Vector index size counts toward storage quota

Azure SQL Serverless

Capabilities (with SQL Server 2025):

Full native vector support when active
Auto-pause when inactive (cost savings)
Auto-scaling compute
Perfect for development and testing with vectors
Pay per second of compute usage

Ideal For:

Development environments with vector search
Proof of concepts for RAG applications
Intermittent vector workloads
Cost-conscious scenarios

Limitations:

Cold start delay (auto-resume takes ~1-2 minutes)
Not suitable for production requiring high availability
Maximum 40 vCores
Storage costs apply when paused (including vector indexes)

Permissions & Security

Required Permissions

For Memory/Vector Store Operations:

-- Database role for read/write operations
ALTER ROLE db_datareader ADD MEMBER [YourAppUser];
ALTER ROLE db_datawriter ADD MEMBER [YourAppUser];

-- Or specific table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.SKMemoryTable TO [YourAppUser];

For Schema Creation:

-- Required if Semantic Kernel creates tables automatically
ALTER ROLE db_ddladmin ADD MEMBER [YourAppUser];

-- Or specific permissions
GRANT CREATE TABLE TO [YourAppUser];
GRANT ALTER ON SCHEMA::dbo TO [YourAppUser];

Azure AD Authentication

Recommended Approach for Azure SQL:

using Microsoft.Data.SqlClient;
using Azure.Identity;

var connectionString = 
    "Server=tcp:your-server.database.windows.net,1433;" +
    "Database=your-database;" +
    "Authentication=Active Directory Default;";

// Uses DefaultAzureCredential chain:
// 1. Managed Identity (in Azure)
// 2. Visual Studio
// 3. Azure CLI
// 4. Environment variables

Managed Identity Setup:

-- Enable Managed Identity for Azure SQL
CREATE USER [your-app-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-app-name];
ALTER ROLE db_datawriter ADD MEMBER [your-app-name];
ALTER ROLE db_ddladmin ADD MEMBER [your-app-name];

Security Best Practices

Always use encrypted connections - Encrypt=true in connection string
Use Managed Identity - Avoid credentials in code
Implement Row-Level Security - For multi-tenant scenarios
Enable Dynamic Data Masking - For sensitive embedding metadata
Use Always Encrypted - For highly sensitive data (note: can’t search encrypted vectors)

Current Limitations

SQL Server 2025 Preview Limitations

Preview Status Considerations:

Production Readiness
- Currently in Preview - not recommended for production yet
- Features may change before general availability
- Requires PREVIEW_FEATURES database configuration
Known Issues
- Some datasets may encounter STRING_AGG or DiskANN graph build errors
- Fix identified for future preview releases
- Test with your specific dataset before full migration
Platform Availability
- SQL Server 2025 Preview on Windows/Linux
- Azure SQL Managed Instance (requires SQL Server 2025 or Always-up-to-date policy)
- Limited availability in other Azure SQL tiers during preview

DiskANN Index Characteristics:

Storage Requirements
- Vector indexes require additional disk space (≈3x memory size on disk)
- Memory usage for in-memory graph structures
- Consider storage tier when planning large vector datasets
Index Build Time
- Initial index creation can be time-consuming for large datasets
- Background process - doesn’t block queries
- Rebuild may be needed after significant data changes

SQL Server 2019-2022 Limitations

Performance Limitations:

No Native Vector Indexing
- Similarity search is O(n) - scans all vectors
- Performance degrades with large datasets (>100k vectors)
- Pre-filtering with WHERE clauses is essential
Memory Constraints
- Large vector comparisons consume memory
- Batch operations may hit memory limits
- Consider pagination for large result sets
Scalability Concerns
- Horizontal scaling requires manual partitioning
- Read replicas help with query distribution
- Write scaling limited by single-master architecture

Feature Limitations:

No Built-in ANN Algorithms
- Must implement custom similarity calculations
- No HNSW, IVF, or DiskANN support
- Alternative: Use column store indexes for better scan performance
Manual Distance Functions
- Must implement cosine similarity via T-SQL or CLR
- No built-in vector distance functions
- Performance of custom functions is not optimized
Vector Dimension Constraints
- VARBINARY(MAX) supports up to 2GB per row
- Practical limit ~32,000 dimensions (float32)
- Most embeddings (384-4096 dimensions) work fine

SQL Server 2025 Native Vector Implementation

1. Enable Preview Features

-- Required for SQL Server 2025 Preview vector features
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
GO

2. Create Table with VECTOR Data Type

-- Create table with native VECTOR column
CREATE TABLE dbo.DocumentEmbeddings (
    id INT PRIMARY KEY IDENTITY(1,1),
    document_id NVARCHAR(50) NOT NULL,
    title NVARCHAR(200),
    content NVARCHAR(MAX),
    embedding VECTOR(1536), -- OpenAI text-embedding-ada-002 dimensions
    category NVARCHAR(50),
    created_at DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_Category NONCLUSTERED (category)
);
GO

-- Example with float16 for higher dimensions (e.g., text-embedding-3-large)
CREATE TABLE dbo.LargeEmbeddings (
    id INT PRIMARY KEY IDENTITY(1,1),
    content NVARCHAR(MAX),
    embedding VECTOR(3072, float16), -- Up to 3,996 dimensions with float16
    created_at DATETIME2 DEFAULT GETUTCDATE()
);
GO

3. Insert Vectors

-- Insert vector data - automatic JSON to VECTOR conversion
INSERT INTO dbo.DocumentEmbeddings (document_id, title, content, embedding, category)
VALUES (
    'doc001',
    'SQL Server Vector Support',
    'SQL Server 2025 introduces native vector capabilities...',
    '[0.123, -0.456, 0.789, ...]', -- JSON array auto-converts to VECTOR
    'database'
);

-- Using JSON_ARRAY function
DECLARE @embedding VECTOR(1536) = JSON_ARRAY(0.1, 0.2, 0.3, /* ... */);
INSERT INTO dbo.DocumentEmbeddings (document_id, title, embedding)
VALUES ('doc002', 'Document Title', @embedding);

-- Using AI_GENERATE_EMBEDDINGS (if external model configured)
DECLARE @text NVARCHAR(MAX) = N'SQL Server 2025 vector search capabilities';
DECLARE @embedding VECTOR(1536) = AI_GENERATE_EMBEDDINGS(@text USE MODEL Ada2Embeddings);

INSERT INTO dbo.DocumentEmbeddings (document_id, content, embedding)
VALUES ('doc003', @text, @embedding);

4. Exact Vector Search (Small Datasets < 50k)

-- Exact k-NN search using VECTOR_DISTANCE
-- Recommended for: &lt; 50,000 vectors, or filtered queries reducing to &lt; 50k results
DECLARE @queryVector VECTOR(1536) = '[0.234, -0.567, 0.890, ...]';

SELECT TOP 10
    id,
    document_id,
    title,
    VECTOR_DISTANCE('cosine', @queryVector, embedding) AS distance
FROM dbo.DocumentEmbeddings
WHERE category = 'database' -- Pre-filter to reduce search space
ORDER BY distance;

-- Alternative distance metrics
SELECT TOP 10
    title,
    VECTOR_DISTANCE('euclidean', @queryVector, embedding) AS euclidean_dist,
    VECTOR_DISTANCE('dot', @queryVector, embedding) AS dot_product
FROM dbo.DocumentEmbeddings
ORDER BY VECTOR_DISTANCE('cosine', @queryVector, embedding);

5. Create DiskANN Vector Index (Large Datasets > 50k)

Note: DiskANN is Microsoft Research’s graph-based ANN algorithm optimized for disk/SSD storage. Like HNSW, it builds a navigable graph, but with different trade-offs optimized for limited memory scenarios. Both achieve excellent recall (95-99%) with sub-100ms queries.

-- Create DiskANN-based vector index for approximate nearest neighbor search
CREATE VECTOR INDEX vec_idx 
ON dbo.DocumentEmbeddings(embedding)
WITH (
    metric = 'cosine', -- Distance metric: 'cosine', 'euclidean', or 'dot'
    type = 'diskANN'
);
GO

-- Index creation runs in background - check progress
SELECT 
    name,
    type_desc,
    is_disabled,
    has_filter
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.DocumentEmbeddings')
    AND name = 'vec_idx';

6. Approximate Vector Search with VECTOR_SEARCH

-- ANN search using vector index - recommended for &gt; 50,000 vectors
DECLARE @queryVector VECTOR(1536) = '[0.234, -0.567, 0.890, ...]';

SELECT
    t.id,
    t.document_id,
    t.title,
    t.content,
    s.distance
FROM
    VECTOR_SEARCH(
        TABLE = dbo.DocumentEmbeddings AS t,
        COLUMN = embedding,
        SIMILAR_TO = @queryVector,
        METRIC = 'cosine',
        TOP_N = 10
    ) AS s
ORDER BY s.distance;

7. Filtered Vector Search

-- Combine vector search with traditional SQL filtering
DECLARE @queryVector VECTOR(1536) = '[0.234, -0.567, 0.890, ...]';

-- Using exact search with pre-filtering (&lt; 50k after filter)
SELECT TOP 10
    id,
    title,
    category,
    VECTOR_DISTANCE('cosine', @queryVector, embedding) AS distance
FROM dbo.DocumentEmbeddings
WHERE 
    category = 'database'
    AND created_at >= DATEADD(month, -6, GETUTCDATE())
ORDER BY distance;

-- Using ANN search (for larger filtered sets)
-- Note: Filter in outer query or use indexed columns for best performance
SELECT
    t.id,
    t.title,
    t.category,
    s.distance
FROM
    VECTOR_SEARCH(
        TABLE = dbo.DocumentEmbeddings AS t,
        COLUMN = embedding,
        SIMILAR_TO = @queryVector,
        METRIC = 'cosine',
        TOP_N = 50 -- Get more candidates
    ) AS s
WHERE t.category IN ('database', 'ai')
ORDER BY s.distance
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

8. Hybrid Search (Vector + Full-Text)

-- Create full-text index for hybrid search
CREATE FULLTEXT INDEX ON dbo.DocumentEmbeddings(content)
    KEY INDEX PK__DocumentEmb__id
    WITH STOPLIST = SYSTEM;
GO

-- Hybrid search: combine vector similarity with full-text relevance
DECLARE @queryVector VECTOR(1536) = '[0.234, -0.567, 0.890, ...]';
DECLARE @searchText NVARCHAR(100) = 'database optimization';

WITH VectorResults AS (
    SELECT
        t.id,
        t.title,
        t.content,
        s.distance AS vector_distance
    FROM VECTOR_SEARCH(
        TABLE = dbo.DocumentEmbeddings AS t,
        COLUMN = embedding,
        SIMILAR_TO = @queryVector,
        METRIC = 'cosine',
        TOP_N = 50
    ) AS s
),
TextResults AS (
    SELECT
        id,
        title,
        content,
        RANK AS text_rank
    FROM dbo.DocumentEmbeddings
    WHERE CONTAINS(content, @searchText)
)
SELECT 
    COALESCE(v.id, t.id) AS id,
    COALESCE(v.title, t.title) AS title,
    COALESCE(v.content, t.content) AS content,
    ISNULL(v.vector_distance, 1.0) AS vector_distance,
    ISNULL(t.text_rank, 0) AS text_rank,
    -- Reciprocal Rank Fusion (RRF) scoring
    (1.0 / (60.0 + ISNULL(v.vector_distance * 100, 100))) +
    (1.0 / (60.0 + ISNULL(100 - t.text_rank, 100))) AS rrf_score
FROM VectorResults v
FULL OUTER JOIN TextResults t ON v.id = t.id
ORDER BY rrf_score DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

9. Performance Comparison: Exact vs ANN

-- Benchmark exact search vs ANN search
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

DECLARE @queryVector VECTOR(1536) = '[0.234, -0.567, 0.890, ...]';

-- Exact search (100% recall)
SELECT TOP 10
    id, title,
    VECTOR_DISTANCE('cosine', @queryVector, embedding) AS distance
FROM dbo.DocumentEmbeddings
ORDER BY distance;

-- ANN search (95-99% recall, much faster for large datasets)
SELECT t.id, t.title, s.distance
FROM VECTOR_SEARCH(
    TABLE = dbo.DocumentEmbeddings AS t,
    COLUMN = embedding,
    SIMILAR_TO = @queryVector,
    METRIC = 'cosine',
    TOP_N = 10
) AS s
ORDER BY s.distance;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

10. Vector Index Maintenance

-- Rebuild vector index after significant data changes
ALTER INDEX vec_idx ON dbo.DocumentEmbeddings REBUILD;

-- Check vector index statistics
SELECT 
    i.name AS index_name,
    i.type_desc,
    ps.index_level,
    ps.page_count,
    ps.record_count,
    ps.avg_page_space_used_in_percent
FROM sys.indexes i
INNER JOIN sys.dm_db_index_physical_stats(
    DB_ID(), 
    OBJECT_ID('dbo.DocumentEmbeddings'), 
    NULL, NULL, 'DETAILED'
) ps ON i.index_id = ps.index_id
WHERE i.name = 'vec_idx';

-- Drop vector index if no longer needed
DROP INDEX vec_idx ON dbo.DocumentEmbeddings;

Semantic Kernel Implementation

1. Setup and Connection

using Microsoft.SemanticKernel;
using Microsoft.SemanticKernel.Connectors.SqlServer;
using Microsoft.SemanticKernel.Memory;

// Connection string for SQL Server
var connectionString = 
    "Server=your-server.database.windows.net;" +
    "Database=VectorDB;" +
    "Authentication=Active Directory Default;" +
    "Encrypt=true;";

// Create memory store
var memoryStore = await SqlServerMemoryStore.ConnectAsync(
    connectionString: connectionString,
    schema: "dbo",
    tableNamePrefix: "SK"
);

2. Azure SQL Serverless Configuration

// Serverless-optimized connection string
var serverlessConnectionString = 
    "Server=tcp:your-server.database.windows.net,1433;" +
    "Database=your-db;" +
    "Authentication=Active Directory Default;" +
    "Encrypt=true;" +
    "ConnectRetryCount=3;" +
    "ConnectRetryInterval=10;" + // Handle auto-resume delay
    "MinPoolSize=0;" + // Allow connection pool to empty (auto-pause)
    "MaxPoolSize=100;";

3. Create Memory with Embeddings

// Build kernel with memory
var kernel = Kernel.CreateBuilder()
    .AddAzureOpenAITextEmbeddingGeneration(
        deploymentName: "text-embedding-ada-002",
        endpoint: "https://your-openai.openai.azure.com/",
        credentials: new DefaultAzureCredential())
    .Build();

// Create memory from store
var memory = new MemoryBuilder()
    .WithMemoryStore(memoryStore)
    .WithTextEmbeddingGeneration(
        kernel.GetRequiredService<ITextEmbeddingGenerationService>())
    .Build();

4. Store Knowledge Documents

// Save documents with embeddings
await memory.SaveInformationAsync(
    collection: "TechnicalDocs",
    id: "doc001",
    text: "SQL Server supports vector storage using VARBINARY columns...",
    description: "SQL Server Vector Storage Overview",
    additionalMetadata: "category:database"
);

await memory.SaveInformationAsync(
    collection: "TechnicalDocs",
    id: "doc002",
    text: "Azure SQL Serverless automatically scales compute resources...",
    description: "Azure SQL Serverless Benefits"
);

5. Vector Search Query

// Search for relevant information
var searchResults = memory.SearchAsync(
    collection: "TechnicalDocs",
    query: "How does SQL Server handle vector search?",
    limit: 5,
    minRelevanceScore: 0.7
);

await foreach (var result in searchResults)
{
    Console.WriteLine($"[{result.Relevance:P}] {result.Metadata.Description}");
    Console.WriteLine($"Content: {result.Metadata.Text}");
    Console.WriteLine($"Metadata: {result.Metadata.AdditionalMetadata}");
    Console.WriteLine();
}

6. Collection Management

// List all collections
var collections = memoryStore.GetCollectionsAsync();
await foreach (var collection in collections)
{
    Console.WriteLine($"Collection: {collection}");
}

// Check if collection exists
bool exists = await memoryStore.DoesCollectionExistAsync("TechnicalDocs");

// Delete a collection
await memoryStore.DeleteCollectionAsync("TechnicalDocs");

7. Batch Operations for Performance

// Batch insert for better performance
var documents = new[]
{
    new { Id = "doc001", Text = "Content 1", Description = "Desc 1" },
    new { Id = "doc002", Text = "Content 2", Description = "Desc 2" },
    new { Id = "doc003", Text = "Content 3", Description = "Desc 3" }
};

foreach (var doc in documents)
{
    await memory.SaveInformationAsync(
        collection: "TechnicalDocs",
        id: doc.Id,
        text: doc.Text,
        description: doc.Description
    );
}

// Note: For large batches, consider manual SqlBulkCopy

8. Filtered Search with Metadata

// While Semantic Kernel doesn't directly support metadata filtering,
// you can implement custom filtering by querying the underlying table

using Microsoft.Data.SqlClient;

var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

// Custom query with pre-filtering
var command = new SqlCommand(@"
    SELECT TOP 10 
        [Key], 
        [MetadataString],
        [Embedding],
        -- Calculate cosine similarity
        (SELECT SUM(a.value * b.value) 
         FROM OPENJSON(@queryVector) a
         CROSS APPLY OPENJSON(CAST([Embedding] AS nvarchar(max))) b
         WHERE a.[key] = b.[key]) / 
        (SQRT((SELECT SUM(POWER(value, 2)) FROM OPENJSON(@queryVector))) *
         SQRT((SELECT SUM(POWER(value, 2)) FROM OPENJSON(CAST([Embedding] AS nvarchar(max)))))) 
        AS Similarity
    FROM [dbo].[SKTechnicalDocsMemory]
    WHERE [MetadataString] LIKE '%category:database%'
    ORDER BY Similarity DESC
", connection);

command.Parameters.AddWithValue("@queryVector", queryVectorJson);

using var reader = await command.ExecuteReaderAsync();
// Process results...

9. Performance Optimization

// Create indexes on frequently filtered columns
// Run this once during setup
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

var createIndexCommand = new SqlCommand(@"
    -- Index on metadata for filtering
    CREATE NONCLUSTERED INDEX IX_Metadata 
    ON [dbo].[SKTechnicalDocsMemory]([MetadataString]);
    
    -- Columnstore index for better scan performance
    CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Embeddings_CS
    ON [dbo].[SKTechnicalDocsMemory]([Embedding]);
", connection);

await createIndexCommand.ExecuteNonQueryAsync();

10. Connection Resiliency for Serverless

using Polly;
using Polly.Retry;

// Retry policy for serverless auto-resume
var retryPolicy = Policy
    .Handle<SqlException>(ex => 
        ex.Number == -2 || // Timeout
        ex.Number == 40613 || // Database unavailable
        ex.Number == 40197) // Connection lost
    .WaitAndRetryAsync(
        retryCount: 3,
        sleepDurationProvider: attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)),
        onRetry: (exception, timeSpan, retryCount, context) =>
        {
            Console.WriteLine($"Retry {retryCount} after {timeSpan.Seconds}s due to: {exception.Message}");
        });

// Use policy with memory operations
await retryPolicy.ExecuteAsync(async () =>
{
    await memory.SaveInformationAsync(
        collection: "TechnicalDocs",
        id: "doc001",
        text: "Document content...",
        description: "Document description"
    );
});

Performance Recommendations

SQL Server 2025 Preview

Small Datasets (< 50k vectors)

Recommended Approach: Exact search with VECTOR_DISTANCE
No index needed - Table scan is efficient at this scale
Expected Performance: Sub-second queries
Use Case: Development, small knowledge bases, filtered searches
Any SQL tier works well - Including serverless for cost savings

-- Optimal for &lt; 50k vectors
SELECT TOP 10 id, title, VECTOR_DISTANCE('cosine', @query, embedding) AS dist
FROM DocumentEmbeddings
ORDER BY dist;

Medium Datasets (50k - 1M vectors)

Recommended Approach: DiskANN vector index with VECTOR_SEARCH
Index Required: CREATE VECTOR INDEX with type=‘diskANN’
Expected Performance: Sub-100ms queries
Azure SQL Tier: Standard S3+ or Premium
Recall: 95-99% (excellent for most AI applications)

-- Create index once
CREATE VECTOR INDEX vec_idx ON DocumentEmbeddings(embedding)
WITH (metric = 'cosine', type = 'diskANN');

-- Fast ANN search
SELECT t.id, t.title, s.distance
FROM VECTOR_SEARCH(
    TABLE = DocumentEmbeddings AS t,
    COLUMN = embedding,
    SIMILAR_TO = @query,
    METRIC = 'cosine',
    TOP_N = 10
) AS s;

Large Datasets (1M - 100M vectors)

Recommended Approach: DiskANN with pre-filtering
Azure SQL Tier: Premium P2+ or Business Critical
Expected Performance: 100-500ms with proper indexing
Optimization: Partition by category/tenant
Consider: Read replicas for query distribution

-- Combine index with filtering
SELECT t.id, t.title, s.distance
FROM VECTOR_SEARCH(
    TABLE = DocumentEmbeddings AS t,
    COLUMN = embedding,
    SIMILAR_TO = @query,
    METRIC = 'cosine',
    TOP_N = 50
) AS s
WHERE t.category = @userCategory -- Use indexed column
ORDER BY s.distance
FETCH NEXT 10 ROWS ONLY;

Very Large Datasets (> 100M vectors)

Recommended Approach: DiskANN with Hyperscale tier
Azure SQL Tier: Hyperscale with read replicas
Expected Performance: Sub-second with proper architecture
Optimization: Partition data, use read replicas
Alternative: Consider specialized vector DB if vector-only workload

SQL Server 2019-2022 (Legacy)

Small Datasets (< 10k vectors)

Any SQL tier works
Custom similarity functions
Sub-second query times
Good for development

Medium Datasets (10k-100k vectors)

Use Azure SQL Standard S3+
Implement metadata filtering
Create columnstore indexes
Consider partitioning
Query times: 1-5 seconds

Large Datasets (> 100k vectors)

Not recommended - Migrate to SQL Server 2025 or specialized vector DB
If required: Premium tier, extensive filtering, partitioning
Query times: 5-30+ seconds

Optimization Strategies

Index Selection Strategy:

-- Decision tree for indexing

-- Step 1: Estimate vector count
SELECT COUNT(*) as vector_count FROM DocumentEmbeddings;

-- If &lt; 50,000: Use exact search, no index needed
-- If 50,000 - 100,000: Create DiskANN index, monitor performance
-- If &gt; 100,000: DiskANN index required

-- Step 2: Create appropriate indexes
-- For metadata filtering (always beneficial)
CREATE INDEX IX_Category ON DocumentEmbeddings(category);
CREATE INDEX IX_CreatedAt ON DocumentEmbeddings(created_at);

-- For vector search (if &gt; 50k vectors)
CREATE VECTOR INDEX vec_idx ON DocumentEmbeddings(embedding)
WITH (metric = 'cosine', type = 'diskANN');

Query Optimization Patterns:

-- Pattern 1: Pre-filter before vector search (most efficient)
WITH FilteredSet AS (
    SELECT id, embedding
    FROM DocumentEmbeddings
    WHERE category = @category 
        AND created_at >= @startDate
)
SELECT TOP 10 
    id,
    VECTOR_DISTANCE('cosine', @query, embedding) AS distance
FROM FilteredSet
ORDER BY distance;

-- Pattern 2: Use VECTOR_SEARCH for large unfiltered sets
SELECT t.id, t.title, s.distance
FROM VECTOR_SEARCH(
    TABLE = DocumentEmbeddings AS t,
    COLUMN = embedding,
    SIMILAR_TO = @query,
    METRIC = 'cosine',
    TOP_N = 10
) AS s;

-- Pattern 3: Hybrid - get more candidates, then filter/rerank
WITH Candidates AS (
    SELECT t.id, t.category, t.title, s.distance
    FROM VECTOR_SEARCH(
        TABLE = DocumentEmbeddings AS t,
        COLUMN = embedding,
        SIMILAR_TO = @query,
        METRIC = 'cosine',
        TOP_N = 100
    ) AS s
)
SELECT TOP 10 *
FROM Candidates
WHERE category IN ('tech', 'ai')
    AND distance &lt; 0.3
ORDER BY distance;

Cost Optimization Tips

Use Serverless for Development
- Auto-pause saves costs when inactive
- Full SQL Server 2025 features when active
- Perfect for testing vector workloads
Right-size for Production
- Start with Standard tier, monitor performance
- Upgrade to Premium only if needed
- Use reserved capacity for predictable workloads
Optimize Storage
- Use float16 for larger dimensions (text-embedding-3-large)
- Archive old vectors to cheaper storage tiers
- Monitor vector index size vs storage quota
Query Optimization
- Cache frequent searches in Redis/Memory
- Implement result caching in application layer
- Use connection pooling
- Batch operations where possible
Elastic Pools
- Share resources across multiple databases
- Cost-effective for multiple tenants
- Each tenant can have own vector data

When to Use SQL Server for Vectors

SQL Server 2025 is Excellent For ✅

Primary Use Cases:

Co-located Data - Vectors alongside relational data in single database
Enterprise Applications - Need ACID transactions for vector operations
Existing SQL Infrastructure - Already using SQL Server or Azure SQL
Security & Compliance - Require enterprise-grade security features
Hybrid Search - Combining vector search with full-text search
Structured Metadata - Rich filtering on relational attributes
Small to Large Datasets - From thousands to billions of vectors

Performance Scenarios:

Small datasets (< 50k vectors) with exact search
Medium datasets (50k-1M) with DiskANN indexing
Large datasets (> 1M) with proper index tuning
Sub-100ms query requirements with ANN search
High recall requirements (95-99% with ANN, 100% with exact)

Operational Benefits:

Cost-effective if already using SQL Server licenses
Unified data platform (no separate vector database)
Familiar SQL tooling and expertise
Built-in backup, replication, high availability
Comprehensive monitoring and diagnostics

Security & Governance:

Row-Level Security for multi-tenancy
Always Encrypted, TDE for data protection
Audit trails and compliance logging
Azure AD integration and Managed Identity
Dynamic Data Masking for sensitive metadata

SQL Server 2025 May Not Be Ideal For ⛔

Specialized Requirements:

Cutting-edge Vector Algorithms - Need latest research algorithms
Multi-modal Search - Complex image + text + audio vectors
Global Distribution as Primary Need - Multi-region writes critical
Pure Vector Workload - >80% of queries are vector-only
Extreme Scale - Billions of vectors with millisecond latency

Operational Constraints:

Preview Concerns - Can’t use preview features in production (until GA)
Learning Curve - Team has no SQL Server experience
Platform Lock-in - Need database-agnostic solution

Performance Extremes:

Massive Throughput - >10,000 vector queries/second sustained
Ultra-low Latency - Need <10ms p99 latency at scale
Extreme Dimensions - >10,000 dimensions per vector

Alternative Recommendations

Choose Azure AI Search when:

Need managed AI features (semantic ranking, integrated vectorization)
Hybrid search is the primary use case
Want built-in data chunking and reranking
Prefer fully managed service with minimal configuration

Choose Azure Cosmos DB when:

Global distribution with multi-region writes required
Document-oriented data model fits better
Need serverless consumption with auto-scale
Prefer NoSQL/MongoDB API

Choose Qdrant/Pinecone/Weaviate when:

Vector search is the dominant workload (>80%)
Need bleeding-edge vector algorithms
Require specialized vector features (collections, payloads, filters)
Want vendor-specific optimizations

Choose PostgreSQL with pgvector when:

Need open-source solution
Want portability across cloud providers
Have existing PostgreSQL expertise
Prefer HNSW or IVF indexing

Decision Framework

Ask These Questions:

Do you already use SQL Server?
- Yes → Strong case for SQL Server 2025
- No → Evaluate based on other factors
Is your dataset size < 1M vectors?
- Yes → SQL Server 2025 performs excellently
- No → SQL Server 2025 still viable, but compare with alternatives
Do vectors need to be co-located with relational data?
- Yes → SQL Server 2025 is ideal
- No → Specialized vector DB may be simpler
Are enterprise security features critical?
- Yes → SQL Server 2025 has mature security
- No → Other options may suffice
Can you use preview features?
- Yes → SQL Server 2025 Preview now
- No → Wait for GA or use SQL Server 2022 for basic storage
What’s your query latency requirement?
- Sub-second → SQL Server 2025 works great
- Sub-100ms → SQL Server 2025 with tuning
- Sub-10ms → May need specialized solution

Scoring Matrix:

Factor	Weight	SQL Server 2025	Azure AI Search	Cosmos DB	Specialized Vector DB
Co-located with relational data	High	⭐⭐⭐⭐⭐	⭐⭐	⭐⭐⭐	⭐
Enterprise security	High	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐
Performance (< 1M vectors)	High	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Performance (> 10M vectors)	Medium	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Ease of use	Medium	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐
Hybrid search	Medium	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐
Cost (existing SQL)	High	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐	⭐⭐
Cost (new deployment)	Medium	⭐⭐⭐	⭐⭐⭐	⭐⭐⭐⭐	⭐⭐

SQL Server 2025 vs Alternatives

SQL Server 2025 Native Vectors

Strengths:

✅ Native VECTOR data type with DiskANN indexing
✅ ACID transactions for vector operations
✅ Store vectors alongside relational data
✅ Enterprise security (TDE, Always Encrypted, Row-Level Security)
✅ Excellent performance (sub-100ms queries with proper indexing)
✅ High recall (95-99% with ANN, 100% with exact search)
✅ Flexible: Choose exact search (small data) or ANN (large data)
✅ Cost-effective if already using SQL Server infrastructure

Limitations:

⚠️ Currently in Preview (not GA yet)
⚠️ Learning curve for index tuning
⚠️ Storage overhead for vector indexes

Best For:

Applications already using SQL Server/Azure SQL
Need vectors co-located with relational data
Datasets from thousands to billions of vectors
Enterprise compliance and security requirements
RAG applications with structured metadata

Specialized Vector Databases

When to Consider Alternatives:

Azure AI Search - Choose when:

Need managed service with built-in AI features
Require semantic ranking and reranking
Want integrated data chunking and vectorization
Hybrid search is primary use case

Azure Cosmos DB (NoSQL/MongoDB) - Choose when:

Need global distribution and multi-region writes
Document-oriented data model fits better
DiskANN or IVF indexing preferred
Serverless consumption model needed

Qdrant/Pinecone/Weaviate - Choose when:

Vector search is the primary workload (>80% of queries)
Need cutting-edge vector algorithms
Require specific features (multi-tenancy, clustering)
Don’t need tight relational data integration

Recommendation Matrix

Dataset Size	Best Choice	Reason
< 10k vectors	SQL Server 2025 (exact search)	Simple, no index needed, sub-second queries
10k - 100k	SQL Server 2025 (exact or ANN)	Excellent performance, use filtering
100k - 10M	SQL Server 2025 (DiskANN)	Native support, great performance
> 10M vectors	SQL Server 2025 or specialized DB	Both viable - compare specific workload

Integration Need	Best Choice
Co-located with relational data	SQL Server 2025
Standalone vector search	Specialized vector DB
Global distribution	Azure Cosmos DB
Full-text + vectors	SQL Server 2025 or Azure AI Search
Multi-modal search	Azure AI Search

Summary

SQL Server has evolved to provide comprehensive vector capabilities for Semantic Kernel applications. SQL Server 2025 Preview introduces native vector support with the VECTOR data type, DiskANN-based indexing, and built-in vector search functions - making it a first-class choice for AI and RAG applications.

Key Takeaways:

For SQL Server 2025 Preview:

✅ Native VECTOR data type with efficient storage
✅ DiskANN indexing for high-performance ANN search
✅ Choose exact search (< 50k vectors) or ANN (> 50k vectors)
✅ Store vectors alongside relational data with ACID guarantees
✅ Enterprise-grade security and compliance
⚠️ Currently in preview - test before production use

For Earlier Versions (2019-2022):

Use for small datasets (< 10k vectors) or development
Implement custom similarity calculations
Consider migration to SQL Server 2025 for production AI workloads

Best Practices:

Choose the Right Search Method
- Exact search (VECTOR_DISTANCE): < 50,000 vectors, 100% recall
- ANN search (VECTOR_SEARCH): > 50,000 vectors, 95-99% recall
Optimize Performance
- Create DiskANN indexes for large datasets
- Use pre-filtering with WHERE clauses
- Combine with full-text search for hybrid scenarios
- Monitor index statistics and rebuild as needed
Plan for Scale
- Start with exact search, add DiskANN index when needed
- Use Azure SQL for managed infrastructure
- Consider serverless for development and cost savings
- Evaluate specialized vector DBs if >10M vectors with complex requirements
Security & Compliance
- Leverage SQL Server’s enterprise security features
- Use Managed Identity for Azure SQL
- Implement Row-Level Security for multi-tenant scenarios

When SQL Server 2025 is the Right Choice:

Already using SQL Server or Azure SQL
Need vectors co-located with relational data
Enterprise security and compliance requirements
Datasets from thousands to billions of vectors
Cost-effective for existing SQL infrastructure
ACID transactions for vector operations

When to Consider Alternatives:

Cutting-edge vector algorithms needed
Global distribution primary requirement
Vector search is >80% of workload
Managed AI features preferred (Azure AI Search)

SQL Server 2025’s native vector support bridges the gap between traditional relational databases and specialized vector stores, offering enterprise-grade vector search capabilities directly in your database. The Semantic Kernel SQL Server connector (when updated for 2025 features) will handle the complexity of vector storage and retrieval, letting you focus on building intelligent applications while leveraging SQL Server’s robust infrastructure.