Sql Server Vector

Description

SQL Server can be used as the backing implementation for Flex vector similarity search. Your application code should depend on IFlexVectorStore, while the provider implementation handles table access and SQL details.

Important concepts

• IFlexVectorStore is the contract: app code performs upserts and similarity search through the shared interface.

• Upsert + search: generated handlers typically call UpsertAsync(id, vector, metadata, content), and generated queries call SearchAsync(queryVector, topK, filter, minScore).

• Collections (optional): the SQL Server vector provider also supports IFlexVectorStoreWithCollections for provisioning operations (e.g., creating a table/collection).

Configuration in DI

Add the provider in your DI composition root (commonly in EndPoints/...CommonConfigs/OtherApplicationServicesConfig.cs or wherever you centralize registrations).

// using Sumeru.Flex; // IFlexVectorStore

public static class OtherApplicationServicesConfig
{
	public static IServiceCollection AddOtherApplicationServices(
		this IServiceCollection services,
		IConfiguration configuration)
	{
		var section = configuration.GetSection("FlexBase:DataStores:Vector:SqlServer");

		var connectionString = section.GetValue<string>("ConnectionString");
		var tableName = section.GetValue<string>("TableName");
		var dimensions = section.GetValue<int?>("Dimensions") ?? 1536;

		services.AddFlexSqlServerVectorStore(
			connectionString: connectionString!,
			tableName: tableName!,
			dimensions: dimensions);

		// Flex auto-wires generated Queries/Handlers/Plugins that *use* IFlexVectorStore.
		return services;
	}
}

appsettings.json

Configuration is read from FlexBase:DataStores:Vector:SqlServer.

{
  "FlexBase": {
	"DataStores": {
	  "Vector": {
		"SqlServer": {
		  "ConnectionString": "Server=localhost;Database=your-db;User Id=sa;Password=<store-in-secrets>;TrustServerCertificate=True",
		  "TableName": "your_vector_table",
		  "Dimensions": 1536,
		  "Metric": "Cosine",
		  "AutoCreateTable": false
		}
	  }
	}
  }
}

Examples (template-based)

These examples mirror the generated Query and PostBus handler templates. You do not register these types manually—Flex discovers and wires generated Queries/Handlers/Plugins automatically.

Similarity search (Query)

using Microsoft.Extensions.Logging;
using Sumeru.Flex;
using System;
using System.Linq;
using System.Threading.Tasks;

namespace {YourApplication}.Queries.Vector;

public class SimilaritySearchGetSingle : FlexiQueryBridgeAsync<VectorSearchHitDto>
{
	protected readonly ILogger<SimilaritySearchGetSingle> _logger;
	protected readonly IFlexHost _flexHost;
	protected readonly IFlexVectorStore _vectorStore;
	protected SimilaritySearchGetSingleParams _params;
	protected FlexAppContextBridge _flexAppContext;

	public SimilaritySearchGetSingle(
		ILogger<SimilaritySearchGetSingle> logger,
		IFlexHost flexHost,
		IFlexVectorStore vectorStore)
	{
		_logger = logger;
		_flexHost = flexHost;
		_vectorStore = vectorStore;
	}

	public virtual SimilaritySearchGetSingle AssignParameters(SimilaritySearchGetSingleParams @params)
	{
		_params = @params;
		return this;
	}

	public virtual async Task<VectorSearchHitDto?> Fetch()
	{
		_flexAppContext = _params.GetAppContext();

		var results = await _vectorStore.SearchAsync(
			_params.QueryVector,
			topK: 1,
			filter: _params.Filter,
			minScore: _params.MinScore);

		var first = results.FirstOrDefault();
		if (first == null)
			return null;

		return new VectorSearchHitDto
		{
			Id = first.Id,
			Vector = first.Vector,
			Metadata = first.Metadata,
			Content = first.Content,
			Score = first.Score
		};
	}
}

public class SimilaritySearchGetSingleParams : DtoBridge
{
	public ReadOnlyMemory<float> QueryVector { get; set; }
	public FlexVectorFilter? Filter { get; set; }
	public float? MinScore { get; set; }
}

public class VectorSearchHitDto
{
	public string Id { get; set; }
	public ReadOnlyMemory<float> Vector { get; set; }
	public object? Metadata { get; set; }
	public string? Content { get; set; }
	public float Score { get; set; }
}

Upsert a vector record (PostBus handler)

using Microsoft.Extensions.Logging;
using Sumeru.Flex;
using System.Threading.Tasks;

namespace {YourApplication}.PostBusHandlers.Vector;

public partial class UpsertVectorRecordHandler : IUpsertVectorRecordHandler
{
	protected string EventCondition = "";

	protected readonly ILogger<UpsertVectorRecordHandler> _logger;
	protected readonly IFlexHost _flexHost;
	protected readonly IFlexVectorStore _vectorStore;

	protected FlexAppContextBridge? _flexAppContext;

	public UpsertVectorRecordHandler(
		ILogger<UpsertVectorRecordHandler> logger,
		IFlexHost flexHost,
		IFlexVectorStore vectorStore)
	{
		_logger = logger;
		_flexHost = flexHost;
		_vectorStore = vectorStore;
	}

	public virtual async Task Execute(UpsertVectorRecordCommand cmd, IFlexServiceBusContext serviceBusContext)
	{
		_flexAppContext = cmd.Dto.GetAppContext();  //do not remove this line

		await _vectorStore.UpsertAsync(
			cmd.Dto.Id,
			cmd.Dto.Vector,
			cmd.Dto.Metadata,
			cmd.Dto.Content);

		await this.Fire(EventCondition, serviceBusContext);
	}
}

SQL Server considerations

• Ensure your SQL Server version supports vector operations for your chosen approach.

• If you want Flex to create the table on startup, use AddFlexSqlServerVectorStoreWithAutoCreate(...) and choose a FlexVectorDistanceMetric.

• Dimensions must match the embedding model you use.