Efficiently and Elegantly Modeling Embeddings in Azure SQL and SQL Server

Storing and querying text embeddings in a database it might seem challenging, but with the right schema design, it’s not only possible, it’s powerful. Whether you’re building AI-powered search, semantic filtering, or recommendation features, embeddings, and thus vectors, are now a first-class data type. So how do you model them well inside a database like SQL Server and Azure SQL?

In this post, I’ll walk you through a practical, scalable strategy to store embeddings effectively—while keeping your schema clean, your queries readable, and your operations efficient.

This blog post is part of a series of blogs I’m writing as I’m learning how to bring AI to the database, which is the most efficient way to use AI with your own data. Make sure to check out the entire series as you’ll find answers to very common questions.

The Challenge of Managing Embeddings in a Database

Embeddings are high-dimensional vectors that represent the meaning of text. When you want to store and search them in a database environment, you quickly hit a few common challenges.

First, embeddings can be large—often several kilobytes per vector—which means they unlikely belong in your core table structure. Second, if your text content is long (like an article, a contract, or a code snippet with detailed notes), you usually need to chunk the text and generate multiple embeddings per record. Finally, many real-world records include multiple text fields—such as description, notes, and details—each of which might need to be embedded separately. This rules out the idea of having a single “embedding column” directly embedded in your main data table.

What you need instead is a flexible and maintainable model that accounts for all of this complexity without becoming a mess of JOINs and tangled logic.

The Right Way: One Embedding Table per Text Field

The most effective approach is to create one separate embedding table for each long-text field that needs to be vectorized. Each table holds the chunked embeddings for a specific field—so for example, all description embeddings go into a code_samples_description_embeddings table, all note embeddings into code_samples_notes_embeddings, and so on.

Here’s what the base samples table might look like:

create table dbo.code_samples
(
    id int identity primary key,
    name nvarchar(100) not null,
    description nvarchar(max) not null,
    notes nvarchar(max) null,
    details json null,
    url nvarchar(1000) not null,
    created_on datetime2 not null,
    updated_on datetime2 not null
)

Then, each long-form field gets its own embedding table. For example for the description field I created the following table to store the related embeddings:

create table dbo.code_samples_description_embeddings (
    id int not null,
    chunk_index int not null,
    embedding vector(1536) not null,
    primary key (id, chunk_index)
)

Similar tables will be created for the notes and the details columns

This gives you the flexibility to handle large, multi-paragraph text inputs while keeping embeddings organized by field and easily manageable.

Elegant Querying with LEAST()

Let’s say you want to find the top-k most relevant code samples for a given query vector. Relevance could come from the description, notes, or details field—you don’t necessarily know which.

Instead of unioning results from all embedding tables, you can use a single query and leverage the LEAST() function to pick the closest match from all available embedding sources:

select top(@k)
    s.id, name, description, notes, details,
    least(
        vector_distance('cosine', de.embedding, @qv),
        vector_distance('cosine', ne.embedding, @qv),
        vector_distance('cosine', dte.embedding, @qv)
    ) as distance_score
from
    samples s
inner join
    samples_description_embeddings de on de.id = s.id
left join
    samples_notes_embeddings ne on ne.id = s.id
left join
    samples_details_embeddings dte on dte.id = s.id
order by
    distance_score asc

This is where LEAST() shines. It cleanly expresses the idea: “Get the closest semantic match across all available text fields.”

The resulting query is readable, concise, and easy to maintain. Adding another field? Just add a new LEFT JOIN and another vector_distance() expression inside LEAST().

It’s fantastic case that shows how SQL queries are expressive and elegant.

Embedding Metadata with JSON for Richer Context

While some fields (like descriptions or notes) benefit from chunked embeddings, others—such as tags, author, category, or language—are too short to justify embedding individually. Embedding “Python” or “database tutorial” on their own doesn’t carry much semantic weight.

A better approach is to consolidate these lightweight fields into a single JSON column, and embed the result as one coherent string. This reduces complexity and adds semantic density.

The sample table shown above as a details JSON column that looks like this:

{
    "tags": ["SQL", "vector search"],
    "author": "Alice Smith",
    "language": "T-SQL",
    "category": "database design"
}

Now you only need one embedding operation to capture the full semantic fingerprint of that record’s metadata. This approach is especially useful when metadata varies in shape across records or grows over time. It allows you to semantically represent almost the entire record—long-form fields via chunked embedding tables, and metadata via a single embedded JSON blob—without cluttering your schema or blowing up your compute budget.

Of course, you don’t need to have a JSON column to follow this approach. You can easily generate the JSON data from your table using a FOR JSON or JSON_OBJECT function and generate the JSON on the fly, without needed to store it at all. You’ll be only storing the embedding calculated on the generated JSON.

Wrapping Up

If you’re integrating embeddings into your database, the key is to think modularly and semantically.

Start by breaking out each long-form text field into its own embedding table, allowing chunked storage and flexible vector querying. Use LEAST() function to combine vector distances elegantly at runtime. And for short, structured metadata, consolidate it into a JSON column and generate a single embedding from the flattened content.

This strategy turns your existing structured data platform into a vector-aware, AI-powered backend—without introducing unnecessary complexity or requiring a new database system.

It’s fast. It’s clean. And it works beautifully.

Real-World Example

Everything explained in this post has been used to build a real-world, production-grade sample, freely available on GitHub. The only difference is that, for my use case, I didn’t need to chunk the text, so in the embeddings table you’ll not find the chunk_id, but other than that, the sample is very well aligned with that explained her, (and with many more cool best practices that I’ll explain in future posts….so stay tuned!

https://github.com/yorek/azure-sql-db-ai-samples-search