Generating embeddings

A vector column is only useful once it holds vectors, and vectors come from an embedding model. Hana's first decision was which model to use and where it would run. Her second was what text to send it, because an embedding can only capture what it is given. Her third, which took longest, was how to keep embeddings current when product descriptions change every week.

Choosing an embedding model

The DP-800 outline asks candidates to evaluate external models, including multimodal, multilanguage, size and structured-output capabilities. For embeddings, the main questions are these. Which languages must it understand? Harrow & Finch's guides are in English, but the Canadian stores also serve customers who type in other languages, and a multilingual model maps a question in one language near a passage in another. How many dimensions does it return, and can that be reduced? How long an input does it accept, measured in tokens, the word pieces models work with? Where does it run: a hosted service such as Azure OpenAI in Microsoft Foundry, another provider, or a local runtime? And what does it cost per million tokens?

Hana chose an Azure OpenAI deployment of text-embedding-3-small in Microsoft Foundry, in Canada East, with 1,536 dimensions. Carmen Ortiz approved it on condition that the deployment be a regional Standard deployment, which processes requests in the chosen region, rather than a Global deployment, which may process them in any Azure region; for product text it mattered little, but the team wanted one rule for all data. Nikolai also tested a multilingual open model on a small Ollama server; it was free per call but noticeably weaker on hardware terms, and the team decided running another server was not worth it.

Registering the model

SQL Server 2025 and Azure SQL call embedding models through an external model, a database object created with CREATE EXTERNAL MODEL. It records the endpoint location, the API format, the model type, the model name, the credential to use and any extra parameters. The model type is currently always EMBEDDINGS. The API format is one of Azure OpenAI, OpenAI, Ollama or ONNX Runtime. Only HTTPS endpoints are accepted.

CREATE EXTERNAL MODEL CatalogEmbedder
AUTHORIZATION catalog_owner
WITH (
    LOCATION   = 'https://hf-aoai-cat.openai.azure.com/openai/deployments/te3-small/embeddings?api-version=2024-10-21',
    API_FORMAT = 'Azure OpenAI',
    MODEL_TYPE = EMBEDDINGS,
    MODEL      = 'text-embedding-3-small',
    CREDENTIAL = [https://hf-aoai-cat.openai.azure.com/],
    PARAMETERS = '{"dimensions": 1536, "sql_rest_options": {"retry_count": 3}}'
);

GRANT EXECUTE ON EXTERNAL MODEL::CatalogEmbedder TO role_embedding_writer;

Creating or changing a model needs the CREATE EXTERNAL MODEL or ALTER ANY EXTERNAL MODEL permission; using it needs EXECUTE on the model. That separation lets the DBA team own model definitions while application roles only call them. The sql_rest_options retry count, from 0 to 10, makes the engine retry temporary HTTP failures such as throttling. On SQL Server 2025, outbound REST calls are off until an administrator enables the external rest endpoint enabled option with sp_configure; Azure SQL Database and Fabric have it on by default. The sys.external_models catalog view lists the models a user can see.

Calling AI_GENERATE_EMBEDDINGS

AI_GENERATE_EMBEDDINGS takes a text expression and the name of an external model and returns the embedding as a JSON array, which converts to the vector type. It can be used in a SELECT, an UPDATE, an INSERT ... SELECT or a variable assignment. A PARAMETERS clause can override model-level settings for one call.

-- One value
DECLARE @q vector(1536) = AI_GENERATE_EMBEDDINGS(N'stop the bathroom mirror fogging' USE MODEL CatalogEmbedder);

-- Fill embeddings for products that do not have one yet
INSERT INTO catalog.ProductEmbedding (ProductId, ModelName, EmbeddedAt, SourceHash, Embedding)
SELECT TOP (500) p.ProductId, 'text-embedding-3-small', SYSUTCDATETIME(),
       HASHBYTES('SHA2_256', p.EmbeddingText),
       AI_GENERATE_EMBEDDINGS(p.EmbeddingText USE MODEL CatalogEmbedder)
FROM catalog.vProductForEmbedding AS p
WHERE NOT EXISTS (SELECT 1 FROM catalog.ProductEmbedding AS e WHERE e.ProductId = p.ProductId);

Each row in that INSERT calls the model once, so 18,400 products mean 18,400 HTTPS calls. Hana ran the load in batches of 500 in a loop with a short pause between batches, which kept the deployment's tokens-per-minute quota from throttling the job, and made the load restartable: if it stopped, the next run picked up the rows without embeddings. The extended events ai_generate_embeddings_summary and external_rest_endpoint_summary recorded status codes and errors for each call, which is how she spotted that a dozen products with empty descriptions were returning errors.

What to embed

The DP-800 outline asks which columns to include in embeddings. Embedding the product name alone produced weak results, because names like Pro Series 2 are short and say little. Embedding everything, including SKU, price and stock, made results worse, because numbers and codes add noise to a vector that represents meaning, and prices change daily, which would force constant re-embedding. Hana settled on a view that builds one text per product from the fields that describe what it is and what it is for.

CREATE VIEW catalog.vProductForEmbedding AS
SELECT p.ProductId,
       CONCAT(p.ProductName, N'. Category: ', c.CategoryPath,
              N'. ', p.ShortDescription,
              N' Uses: ', p.UsageNotes,
              N' Features: ', p.FeatureList) AS EmbeddingText
FROM catalog.Product AS p
JOIN catalog.Category AS c ON c.CategoryId = p.CategoryId
WHERE p.IsActive = 1
  AND p.ShortDescription IS NOT NULL;

The principle is to embed what a user might mean and filter or join on what a user might specify. Store, price range, stock and brand are better as ordinary columns used in WHERE clauses, which vector search can apply during the search, as lesson 6 shows. Hana also stored a hash of the embedded text, so a nightly check can find products whose descriptive text changed and only re-embed those.

Chunking long text

Guides are long: the average is about 8,600 characters, and the longest, a 40-page deck-building guide, is over 90,000. An embedding of a whole guide blurs many topics into one vector, and the model also has an input limit. The usual answer is to split documents into chunks of a few hundred to a thousand or so characters, embed each chunk, and search chunks. AI_GENERATE_CHUNKS does the splitting in T-SQL. It currently supports one chunk type, FIXED, which cuts the text into pieces of CHUNK_SIZE characters, with an optional OVERLAP from 0 to 50, the share of the chunk size, out of 100, repeated from the end of the preceding chunk so that a sentence cut at a boundary still appears whole in one chunk. It needs database compatibility level 170.

INSERT INTO guide.GuideChunk (GuideId, ChunkOrder, ChunkText, Embedding)
SELECT g.GuideId, c.chunk_order, c.chunk,
       AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL CatalogEmbedder)
FROM guide.Guide AS g
CROSS APPLY AI_GENERATE_CHUNKS(SOURCE = g.BodyText, CHUNK_TYPE = FIXED,
                               CHUNK_SIZE = 1000, OVERLAP = 10) AS c
WHERE g.GuideId BETWEEN @first AND @last;

Fixed-size chunks are simple but blind to structure: a chunk can start mid-sentence or merge the end of one section with the start of the next. Hana improved results by chunking each guide section separately, using the headings the guides already have, so a chunk never crosses from cutting tiles to grouting. She also prefixed each chunk with its guide and section title before embedding, so a chunk that says apply two coats still carries the context of which job it belongs to.

def fixed_chunks(text, size, overlap):
    """Mimic FIXED chunking: each chunk after the first starts `overlap` parts in 100 of `size` earlier."""
    step = size - size * overlap // 100
    chunks, start = [], 0
    while start < len(text):
        chunks.append(text[start:start + size])
        if start + size >= len(text):
            break
        start += step
    return chunks

guide = "x" * 8_600            # an average guide of 8,600 characters
for size, overlap in [(1000, 0), (1000, 10), (500, 20)]:
    c = fixed_chunks(guide, size, overlap)
    total = sum(len(s) for s in c)
    print(f"size {size}, overlap {overlap}: {len(c)} chunks, {total:,} characters embedded")

size 1000, overlap 0: 9 chunks, 8,600 characters embedded
size 1000, overlap 10: 10 chunks, 9,500 characters embedded
size 500, overlap 20: 22 chunks, 10,700 characters embedded

Smaller chunks give more precise matches but more vectors, more model calls and less context in each passage handed to a language model later. Larger chunks carry more context but blur topics. Hana tried 500, 1,000 and 1,500 characters on the 60 test questions; 1,000 characters with 10 in 100 overlap, chunked per section, gave the best mix, and produced 10,416 chunks from the 1,240 guides.

Keeping embeddings current

Products are added and descriptions edited every week. An embedding generated from old text silently returns wrong matches, so the team needed a maintenance method. The DP-800 outline lists several, and each fits a different need.

A trigger was ruled out quickly: a model call can take a few hundred milliseconds and can fail, and putting it inside the transaction that saves a product would make editing slower and occasionally impossible. Hana chose change tracking with a job that runs every 15 minutes, compares the stored hash with the hash of the current embedding text, and re-embeds only rows whose descriptive text changed. A price change, which touches the Product table but not the embedding text, costs nothing. For the pilot, Elena moved the job to an elastic job agent, as MSAZ-282 described for other scheduled work.