Using the SQL Server Vector Store connector (Preview)

Overview

The SQL Server Vector Store connector can be used to access and manage data in SQL Server. The connector has the following characteristics.

Getting started

Add the SQL Sever Vector Store connector NuGet package to your project.

dotnet add package Microsoft.SemanticKernel.Connectors.SqlServer --prerelease

You can add the vector store to the IServiceCollection dependency injection container using extension methods provided by Semantic Kernel.

using Microsoft.Extensions.DependencyInjection;

// Using IServiceCollection with ASP.NET Core.
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSqlServerVectorStore(_ => "<connectionstring>");

using Microsoft.Extensions.DependencyInjection;
using Microsoft.SemanticKernel;

// Using IServiceCollection with ASP.NET Core.
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSqlServerVectorStore(_ => "<connectionstring>")

You can construct a Sql Server Vector Store instance directly.

using Microsoft.SemanticKernel.Connectors.SqlServer;

var vectorStore = new SqlServerVectorStore("<connectionstring>");

It is possible to construct a direct reference to a named collection.

using Microsoft.SemanticKernel.Connectors.SqlServer;

var collection = new SqlServerCollection<string, Hotel>("<connectionstring>", "skhotels");

Data mapping

The SQL Server Vector Store connector provides a default mapper when mapping from the data model to storage. This mapper does a direct conversion of the list of properties on the data model to the columns in SQL Server.

Property name override

You can provide override property names to use in storage that is different to the property names on the data model. The property name override is done by setting the StorageName option via the data model property attributes or record definition.

Here is an example of a data model with StorageName set on its attributes and how that will be represented in a SQL Server command.

using Microsoft.Extensions.VectorData;

public class Hotel
{
    [VectorStoreKey]
    public ulong HotelId { get; set; }

    [VectorStoreData(StorageName = "hotel_name")]
    public string? HotelName { get; set; }

    [VectorStoreData(StorageName = "hotel_description")]
    public string? Description { get; set; }

    [VectorStoreVector(Dimensions: 4, DistanceFunction = DistanceFunction.CosineDistance)]
    public ReadOnlyMemory<float>? DescriptionEmbedding { get; set; }
}

CREATE TABLE Hotel (
[HotelId] BIGINT NOT NULL,
[hotel_name] NVARCHAR(MAX),
[hotel_description] NVARCHAR(MAX),
[DescriptionEmbedding] VECTOR(4),
PRIMARY KEY ([HotelId])
);

Overview

The SQL Server Vector Store connector is a Vector Store implementation provided by Semantic Kernel that uses Azure SQL as a vector store. Once SQL Server on-prem supports vectors it can also be used with that.

The connector has the following characteristics.

Getting started

Add the Semantic Kernel package to your project.

pip install semantic-kernel[sql]

The SQL Server connector uses the pyodbc package to connect to SQL Server. The extra will install the package, but you will need to install the ODBC driver for SQL Server separately, this differs by platform, see the Azure SQL Documentation for details.

In order for the store and collection to work, it needs a connection string, this can be passed to the constructor or be set in the environment variable SQL_SERVER_CONNECTION_STRING. In order to properly deal with vectors, the LongAsMax=yes option will be added if not found. It also can use both username/password or integrated security, for the latter, the DefaultAzureCredential is used.

In the snippets below, it is assumed that you have a data model class defined named 'DataModel'.

from semantic_kernel.connectors.memory.sql_server import SqlServerStore

vector_store = SqlServerStore()

# OR

vector_store = SqlServerStore(connection_string="Driver={ODBC Driver 18 for SQL Server};Server=server_name;Database=database_name;UID=user;PWD=password;LongAsMax=yes;")

vector_collection = vector_store.get_collection("dbo.table_name", DataModel)

It is possible to construct a direct reference to a named collection.

from semantic_kernel.connectors.memory.sql_server import SqlServerCollection

vector_collection = SqlServerCollection("dbo.table_name", DataModel)

Note: The collection name can be specified as a simple string (e.g. table_name) or as a fully qualified name (e.g. dbo.table_name). The latter is recommended to avoid ambiguity, if no schema is specified, the default schema (dbo) will be used.

When you have specific requirements for the connection, you can also pass in a pyodbc.Connection object to the SqlServerStore constructor. This allows you to use a custom connection string or other connection options:

from semantic_kernel.connectors.memory.sql_server import SqlServerStore
import pyodbc

# Create a connection to the SQL Server database
connection = pyodbc.connect("Driver={ODBC Driver 18 for SQL Server};Server=server_name;Database=database_name;UID=user;PWD=password;LongAsMax=yes;")
# Create a SqlServerStore with the connection
vector_store = SqlServerStore(connection=connection)

You will have to make sure to close the connection yourself, as the store or collection will not do that for you.

Custom create queries

The SQL Server connector is limited to the Flat index type.

The create_collection method on the SqlServerCollection allows you to pass in a single or multiple custom queries to create the collection. The queries are executed in the order they are passed in, no results are returned.

If this is done, there is no guarantee that the other methods still work as expected. The connector is not aware of the custom queries and will not validate them.

If the DataModel has id, content, and vector as fields, then for instance you could create the table like this in order to also create a index on the content field:

from semantic_kernel.connectors.memory.sql_server import SqlServerCollection

# Create a collection with a custom query
async with SqlServerCollection("dbo.table_name", DataModel) as collection:    
    collection.create_collection(
        queries=["CREATE TABLE dbo.table_name (id INT PRIMARY KEY, content NVARCHAR(3000) NULL, vector VECTOR(1536) NULL ) PRIMARY KEY (id);",
        "CREATE INDEX idx_content ON dbo.table_name (content);"]
    )

Coming soon

More info coming soon.