3.2 — Embeddings: Columns, Chunking, Generation and Maintenance
DP-800 Domain 3 · Skill 3.2
What You Need to Know
An embedding is a vector that captures meaning, so similar texts get nearby vectors. Good semantic search depends on what you embed, how you split it, and how you keep it current.
Which columns to embed
- Embed text that carries meaning users search for: product name with description and key attributes, a ticket's subject with its body, an article's title with its content.
- Combine fields into one string with labels ("Name: ... | Category: ... | Description: ...") so the model gets context.
- Don't embed IDs, prices, dates, or values that need exact filters. Keep those as columns and use them in
WHEREclauses next to the vector search. - Leave out sensitive data you wouldn't want sent to the model endpoint.
Chunking
Long documents are split into chunks, and each chunk gets its own embedding. One huge vector dilutes meaning, and models have input limits.
SELECT d.DocID, c.chunk_order, c.chunk,
AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL ProductEmbeddings) AS embedding
FROM dbo.Documents AS d
CROSS APPLY AI_GENERATE_CHUNKS(SOURCE = d.Body, CHUNK_TYPE = FIXED,
CHUNK_SIZE = 1000, OVERLAP = 10) AS c;
AI_GENERATE_CHUNKSreturnschunk,chunk_order,chunk_offsetandchunk_length(pluschunk_set_idwhen enabled).- CHUNK_SIZE is in characters. OVERLAP is a percentage (0–50) of the previous chunk repeated, so meaning isn't cut at a boundary.
- Store chunks in a child table (DocID, ChunkOrder, ChunkText, Embedding) so a search can return the parent document.
- Tune the size: too small loses context, too large blurs meaning. A few hundred to about a thousand characters with some overlap is a common start, then measure.
Generating embeddings
UPDATE p
SET p.Embedding = AI_GENERATE_EMBEDDINGS(p.SearchText USE MODEL ProductEmbeddings)
FROM dbo.Product AS p
WHERE p.Embedding IS NULL;
AI_GENERATE_EMBEDDINGS(text USE MODEL name [PARAMETERS json])calls the external model. Store the result in avector(n)column whose dimensions match the model.- Process large backfills in batches to respect rate limits, and keep retries on.
- Generating embeddings in an application or a pipeline (with the Foundry or OpenAI SDKs) and writing vectors to SQL is also valid, especially at high volume.
Keeping embeddings current
| Method | Character |
|---|---|
| Table trigger | Synchronous and simple, but slows every write and fails with the model. Avoid it for external model calls |
| Change Tracking + scheduled job | Periodically embeds rows that changed since the last version. Simple and batch-friendly |
| Azure Functions SQL trigger binding | Near real time and asynchronous, uses Change Tracking. A common recommendation |
| CDC | Full change history. Useful when an ETL already reads it |
| Change event streaming (CES) | Pushes changes to Event Hubs for scalable event-driven embedding workers |
| Azure Logic Apps | Low-code workflow per change |
| Microsoft Foundry pipelines | Managed ingestion and indexing flows for AI scenarios |
Only re-embed when the embedded text changes (for example, check whether the description changed), not when a price changes.
Key Concept: Embed meaningful text, keep filters as columns, chunk long content with overlap, store vectors in vector(n), and refresh them asynchronously when the source text changes.
Exam Traps
- Embedding a whole 50-page manual as a single vector
- Including the price or stock level in the embedded text and then re-embedding on every price change
- Treating OVERLAP as a number of characters (it's a percentage of CHUNK_SIZE)
- Calling the embedding model synchronously in a trigger on a busy table
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- AI_GENERATE_EMBEDDINGS, Microsoft Learn
- AI_GENERATE_CHUNKS, Microsoft Learn
- Vector search and vector indexes in the SQL Database Engine, Microsoft Learn
- Azure SQL trigger for Functions, Microsoft Learn