How to Generate Embeddings and Store Them in SQL Server

Introduction

Imagine you have 10,000 SQL Server troubleshooting articles, product descriptions, or support tickets. A user searches for:

“Why is my database running slowly?”

A traditional SQL query can search for matching words such as database, slowly, or performance. But what if the article says, “Troubleshooting high CPU usage caused by an inefficient execution plan”?

The wording is different, but the meaning is closely related.

This is where embeddings become useful.

In this article, we’ll learn how to generate embeddings from text, store them in SQL Server, and prepare the data for semantic search and Retrieval-Augmented Generation (RAG).

We’ll use practical examples rather than focusing only on theory.

1. What is an embedding?

An embedding is a list of numbers generated by an AI model to represent the meaning of text.

For example, an embedding model might convert these sentences into numerical vectors:

• “SQL Server is experiencing high CPU usage.”
• “The database processor is overloaded.”
• “How do I bake a chocolate cake?”

The first two sentences discuss similar concepts, so their embeddings may be closer together than either is to the third sentence.

A simplified example might look like this:

Text: SQL Server has high CPU usage

Embedding:
[0.12, -0.34, 0.87, 0.21, ...]

These numbers are illustrative, not actual output from an embedding model.

The model determines the number of dimensions. Different models can produce vectors of different sizes, so the SQL column must match the selected model.

Important: An embedding is not a summary, a keyword list, or text that can be read directly. It is a numerical representation used to compare semantic similarity.

2. The complete workflow

The overall process is straightforward:

1. Collect the text you want to search.
2. Send the text to an embedding model.
3. Receive the numerical vector.
4. Store the original text and vector in SQL Server.
5. Generate an embedding for a user’s search query.
6. Compare the query vector with stored vectors to find relevant records.

For example, a DBA assistant can find relevant troubleshooting articles and use them to help an AI model answer a question.

3. Create a table to store text and embeddings

Let’s create a small table for SQL Server troubleshooting articles.

CREATE TABLE dbo.KnowledgeArticles
(
    ArticleID INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    ArticleText NVARCHAR(MAX) NOT NULL
);
GO

INSERT INTO dbo.KnowledgeArticles
    (Title, ArticleText)
VALUES
(
    N'Troubleshooting High CPU',
    N'Investigate high CPU usage using Query Store,
      execution plans, and relevant DMVs.'
),
(
    N'Troubleshooting Blocking',
    N'Identify blocking sessions, inspect wait types,
      and investigate long-running transactions.'
),
(
    N'Optimizing Slow Queries',
    N'Review execution plans, indexes, statistics,
      and query performance history.'
);
GO

We now have three sample knowledge articles.

At this stage, SQL Server stores only ordinary text. We still need to generate embeddings and save them.

4. Choose where to generate embeddings

There are two common approaches.

Approach A – Generate embeddings inside SQL Server

SQL Server 2025 introduces AI_GENERATE_EMBEDDINGS, which can call a configured external embedding model. This keeps the embedding-generation step accessible through T-SQL.

Approach B – Generate embeddings in an application

A Python, C#, or other application calls an embedding API, receives the vector, and writes it to SQL Server.

The second approach can also be used with older SQL Server versions, provided your application and model provider support the workflow.

The rest of this article covers both options.

5. Approach A: Generate embeddings using SQL Server 2025

SQL Server 2025 supports the VECTOR data type and AI integration features such as CREATE EXTERNAL MODEL and AI_GENERATE_EMBEDDINGS. Availability and configuration differ across SQL Server and Azure SQL platforms. https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-embeddings-transact-sql?view=sql-server-ver17

Step 1: Configure external REST access

On SQL Server 2025, an administrator may need to enable outbound external REST endpoint calls:

EXECUTE sp_configure
    'external rest endpoint enabled', 1;
GO

RECONFIGURE WITH OVERRIDE;
GO

Check the applicable platform documentation and your organization’s security policies before enabling outbound access.

Step 2: Configure credentials and an embedding model

The database needs a configured external model that points to an embedding endpoint, such as a supported Azure OpenAI or OpenAI endpoint.

The exact setup depends on the provider, authentication method, endpoint, and model.

For example, a model definition uses the CREATE EXTERNAL MODEL statement:

CREATE EXTERNAL MODEL MyEmbeddingModel
WITH
(
    LOCATION = '<EMBEDDING_ENDPOINT>',
    API_FORMAT = '<SUPPORTED_API_FORMAT>',
    MODEL_TYPE = EMBEDDINGS,
    MODEL = '<EMBEDDING_MODEL_NAME>',
    CREDENTIAL = [<CREDENTIAL_NAME>]
);

This is a template, not executable SQL as written. Replace the placeholders with values appropriate for your provider. Create the required database-scoped credential first, following the provider’s documentation.

Do not put API keys directly into application code or SQL scripts committed to source control.

Step 3: Test embedding generation

Once the model is configured, try generating an embedding for one sentence:

SELECT AI_GENERATE_EMBEDDINGS
(
    N'SQL Server is experiencing high CPU usage.'
    USE MODEL MyEmbeddingModel
) AS Embedding;

The function returns a vector represented as JSON. The result will contain many numbers, depending on the embedding model.

If the call fails, check the endpoint, credential, permissions, network access, model name, and the model’s supported request format.

Step 4: Create a table with a vector column

Suppose your chosen embedding model returns 1,536 dimensions. You can create a table like this:

CREATE TABLE dbo.ArticleEmbeddings
(
    ArticleID INT PRIMARY KEY,
    Embedding VECTOR(1536) NOT NULL
);
GO

Important: 1536 is an example, not a universal embedding size. Use the actual dimensions returned by your selected model. SQL Server 2025’s VECTOR type supports up to 1,998 dimensions for its default float32 representation. https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type?view=sql-server-ver17

Step 5: Generate and store embeddings

Now generate embeddings for the sample articles and store them in the vector column:

INSERT INTO dbo.ArticleEmbeddings
    (ArticleID, Embedding)
SELECT
    ArticleID,
    AI_GENERATE_EMBEDDINGS
    (
        ArticleText USE MODEL MyEmbeddingModel
    )
FROM dbo.KnowledgeArticles;
GO

This example assumes the model returns the dimension count defined by VECTOR(1536) and that the configured model is compatible with the function.

Verify that the rows were inserted:

SELECT
    a.ArticleID,
    a.Title,
    e.Embedding
FROM dbo.KnowledgeArticles AS a
JOIN dbo.ArticleEmbeddings AS e
    ON e.ArticleID = a.ArticleID;

The vector is stored in SQL Server, while the original text remains available for display, filtering, and retrieval.

For larger datasets, avoid blindly embedding every row in one large transaction. Process records in manageable batches, handle failures, and record which source version has been embedded.

6. Approach B: Generate embeddings using Python and an API

What if you’re using SQL Server 2019 or SQL Server 2022?

You can generate embeddings in your application and store the resulting vectors in SQL Server. This approach also works with SQL Server 2025 if you prefer to keep AI calls outside the database engine.

The workflow looks like this:

<box border radius=”lg” padding={3} gap={2} align=”center”> <box background=”surface-secondary” radius=”md” padding={3} width=”100%” align=”center”> **1. SQL Server**

<text color="secondary" size="sm">Read article text</text>

</box> <icon name=”arrow-down” size=”xl” color=”secondary” /> <box background=”surface” border radius=”md” padding={3} width=”100%” align=”center”> **2. Python application**

<text color="secondary" size="sm">Send text to an embedding API</text>

</box> <icon name=”arrow-down” size=”xl” color=”secondary” /> <box background=”surface” border radius=”md” padding={3} width=”100%” align=”center”> **3. Embedding model**

<text color="secondary" size="sm">Return a numerical vector</text>

</box> <icon name=”arrow-down” size=”xl” color=”secondary” /> <box background=”surface-secondary” radius=”md” padding={3} width=”100%” align=”center”> **4. SQL Server**

<text color="secondary" size="sm">Store the vector with its source text</text>

</box> </box>

Example: Generate an embedding with Python

The following example uses the OpenAI Python SDK. Install the dependencies first:

pip install openai pyodbc

Set your API key in an environment variable rather than hardcoding it.

import os
from openai import OpenAI

client = OpenAI(
    api_key=os.environ["OPENAI_API_KEY"]
)

text = (
    "Investigate high CPU usage using Query Store, "
    "execution plans, and relevant DMVs."
)

response = client.embeddings.create(
    model="text-embedding-3-small",
    input=text
)

embedding = response.data[0].embedding

print("Number of dimensions:", len(embedding))
print("First five values:", embedding[:5])

The returned embedding is a Python list of numbers. The number of dimensions depends on the model and any supported dimension settings.

The example generates the vector but does not yet store it in SQL Server.

Store the generated vector in SQL Server

For SQL Server 2025, you can use the native VECTOR type. For example, if the generated vector has 1,536 dimensions:

CREATE TABLE dbo.ArticleEmbeddings
(
    ArticleID INT PRIMARY KEY,
    Embedding VECTOR(1536) NOT NULL
);

Your application must pass the vector in a format supported by its SQL Server driver. If the driver does not support native vector parameters, you can send the vector as a JSON array and convert it to VECTOR(1536) in SQL Server.

For older SQL Server versions without the native VECTOR type, common alternatives include storing the vector as JSON in NVARCHAR(MAX) or as binary data in VARBINARY(MAX). These approaches require you to implement or provide vector similarity calculations outside the native vector functionality.

Practical advice: Keep the original text in a relational table and store its embedding alongside a stable article or chunk ID. That makes it easier to update text, regenerate embeddings, and return readable search results.

7. Generate embeddings for multiple records

Generating one embedding is useful for testing. A real application may need to process thousands of articles.

With SQL Server 2025, a simple batch can use AI_GENERATE_EMBEDDINGS with a SELECT statement:

INSERT INTO dbo.ArticleEmbeddings
    (ArticleID, Embedding)
SELECT
    ArticleID,
    AI_GENERATE_EMBEDDINGS
    (
        ArticleText USE MODEL MyEmbeddingModel
    )
FROM dbo.KnowledgeArticles
WHERE ArticleID BETWEEN 1 AND 100;

This example processes a range of article IDs. It assumes the target table and model are configured correctly.

For a production workload, don’t run an unbounded embedding-generation query against a large table without testing. Each row may trigger an external model request, which can increase latency and cost.

A safer process is:

1. Select a manageable batch of records.
2. Generate the embeddings.
3. Store successful results.
4. Log failures and retry transient errors.
5. Move to the next batch.

Also, track which source text has changed. You generally need to regenerate an embedding when its underlying text changes, not every time the application runs.

8. What if the article is very long?

Suppose one article contains 20 pages of SQL Server troubleshooting guidance.

Sending an entire document as a single input may exceed the embedding model’s input limit or produce a vector that is less useful for locating a specific passage.

A better approach is to split the article into smaller, meaningful pieces called chunks.

For example:

Chunk ID	Content
1	Symptoms and causes of high CPU
2	Using Query Store to identify expensive queries
3	Reading execution plans
4	Index and statistics troubleshooting
5	Validation and prevention

Each chunk receives its own embedding.

This allows a search to retrieve the relevant section rather than the entire article.

SQL Server 2025 also provides AI_GENERATE_CHUNKS for splitting text before embedding it. The chunking options and supported syntax should be checked against the SQL Server version and model requirements. https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-embeddings-transact-sql?view=sql-server-ver17

A simple chunk storage table could look like this:

CREATE TABLE dbo.ArticleChunks
(
    ChunkID INT IDENTITY(1,1) PRIMARY KEY,
    ArticleID INT NOT NULL,
    ChunkText NVARCHAR(MAX) NOT NULL,
    Embedding VECTOR(1536) NULL
);

Here, Embedding is nullable because you may insert chunks first and generate their embeddings later.

The example assumes a 1,536-dimensional embedding model. Change the dimension to match your model.

9. How do you know if the embedding was stored correctly?

Don’t stop after a successful INSERT. Validate the results.

Check for missing embeddings

SELECT
    a.ArticleID,
    a.Title
FROM dbo.KnowledgeArticles AS a
LEFT JOIN dbo.ArticleEmbeddings AS e
    ON e.ArticleID = a.ArticleID
WHERE e.ArticleID IS NULL;

This identifies articles that don’t have a corresponding embedding row.

Check the number of stored records

SELECT
    (SELECT COUNT(*)
     FROM dbo.KnowledgeArticles) AS ArticleCount,
    (SELECT COUNT(*)
     FROM dbo.ArticleEmbeddings) AS EmbeddingCount;

Matching counts are a useful first check, although they don’t prove that every vector was generated correctly.

Also validate the vector dimensions, the model used, and whether the stored embedding corresponds to the current version of the source text.

10. Common mistakes to avoid

• Using the wrong vector dimension: The column dimension must match the generated embedding.
• Embedding an entire large document: Chunk long documents into useful sections.
• Regenerating everything unnecessarily: Track source changes and embedding status.
• Ignoring API limits and failures: Use manageable batches, retries, and error logging.
• Storing vectors without the source text: Preserve a link to the original article or chunk.
• Sending sensitive text to an external provider without review: Check data classification, access controls, and provider policies.
• Assuming embeddings automatically provide semantic search: Embeddings represent text numerically; you still need a similarity-search step to retrieve relevant records.

11. What comes next? Searching with embeddings

Generating and storing embeddings is only one part of an AI search solution.

When a user enters a question, the application generates an embedding for that question using the same compatible embedding model and dimensions. The system then compares it with the stored vectors and returns the most relevant text.

In SQL Server 2025, VECTOR_DISTANCE helps calculate how similar two vectors are. Vector search and vector indexes may have specific version and feature requirements, so check Microsoft’s documentation before using them in production. https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors?view=sql-server-ver17

The retrieved passages can then be passed to a generative AI model to create a grounded answer. This pattern is commonly called Retrieval-Augmented Generation (RAG).

For example, a DBA could ask:

“What should I check when SQL Server CPU usage suddenly increases?”

The system can retrieve relevant troubleshooting sections from the knowledge base and use them as context for the answer.

Conclusion

Embeddings help SQL Server applications find related information based on meaning, not just matching keywords.

The practical workflow is:

1. Prepare your text.
2. Generate embeddings using SQL Server 2025 or an external application.
3. Store each vector alongside its source record.
4. Validate the stored data and handle updates.
5. Generate query embeddings and use similarity search to retrieve relevant content.

Start with a small dataset of 10–20 articles. Validate the model, dimensions, storage, and retrieval quality before processing thousands of records.

That small proof of concept will give you a much clearer understanding of how embeddings fit into an AI-powered SQL Server solution.