3.6 — Design and Implement Retrieval-Augmented Generation (RAG)
DP-800 Domain 3 · Skill 3.6
What You Need to Know
RAG grounds a language model's answer in your data: retrieve relevant rows, pass them to the model as context, and return the answer. In SQL, the whole flow can run in T-SQL with sp_invoke_external_rest_endpoint.
When RAG fits
| Use RAG when | Don't when |
|---|---|
| Answers must reflect current, private data (orders, policies, manuals) | The question is answered by a deterministic query ("total sales last month": just run SQL) |
| You need citations to the source rows | The model already knows the general fact and freshness doesn't matter |
| The data changes too often for fine-tuning | Strict calculations where generation could add errors |
The pipeline in T-SQL
-- 1. Retrieve: hybrid or vector search for the question
DECLARE @question nvarchar(1000) = N'What is the return policy for opened electronics?';
DECLARE @qv vector(1536) = AI_GENERATE_EMBEDDINGS(@question USE MODEL PolicyEmbeddings);
-- 2. Convert the structured results to JSON for the prompt
DECLARE @context nvarchar(max) = (
SELECT TOP (5) c.DocID, c.ChunkText
FROM dbo.PolicyChunk AS c
ORDER BY VECTOR_DISTANCE('cosine', c.Embedding, @qv)
FOR JSON PATH);
-- 3. Build the prompt and call the chat model
DECLARE @payload nvarchar(max) = JSON_OBJECT(
'messages': JSON_ARRAY(
JSON_OBJECT('role':'system', 'content':'Answer only from the provided policy excerpts. Cite DocID. If unsure, say so.'),
JSON_OBJECT('role':'user', 'content': CONCAT('Question: ', @question, ' Excerpts: ', @context))),
'max_tokens': 400);
DECLARE @response nvarchar(max);
EXEC sp_invoke_external_rest_endpoint
@url = N'https://contoso-ai.openai.azure.com/openai/deployments/gpt-chat/chat/completions?api-version=2024-10-21',
@method = 'POST',
@credential = [https://contoso-ai.openai.azure.com/],
@payload = @payload,
@response = @response OUTPUT;
-- 4. Extract the answer
SELECT JSON_VALUE(@response, '$.result.choices[0].message.content') AS Answer;
Key facts
| Step | Detail |
|---|---|
| Retrieve | Use the best retrieval you have: vector, hybrid with RRF, plus filters such as product line, language or the user's permissions (RLS still applies) |
| Convert to JSON | FOR JSON PATH, JSON_OBJECT, JSON_ARRAYAGG turn rows into compact, structured context. Send only the columns the model needs |
| Prompt | A system message with instructions (ground answers, cite sources, refuse when the context is insufficient) plus the user question and the context. Ask for structured output (a JSON schema) when an app will parse the reply |
| Call | sp_invoke_external_rest_endpoint (HTTPS only). It needs the EXECUTE ANY EXTERNAL ENDPOINT permission. It's turned off by default on SQL Server 2025 and Managed Instance (sp_configure 'external rest endpoint enabled', 1). Authenticate with a database scoped credential, managed identity preferred |
| Extract | The output wraps the provider's JSON in $.result (and HTTP details in $.response.status). Read it with JSON_VALUE or OPENJSON. Check the HTTP status before you trust the content |
Quality and safety
- Limit the context to the top few relevant chunks (token cost and focus).
- Use
@retry_count(or retry logic) for transient 429 and 5xx responses, and add timeouts. - Treat retrieved text as untrusted (prompt injection). Never let model output run SQL without validation.
- Log the question, the retrieved IDs and the answer for evaluation and audit.
Key Concept: RAG = retrieve (vector or hybrid) → JSON context (FOR JSON) → prompt (system rules + question + context) → sp_invoke_external_rest_endpoint → extract with JSON_VALUE('$.result...').
Exam Traps
- Using RAG for a question a plain SQL aggregate answers exactly
- Reading the answer from the top level of
@responseinstead of$.result... - Calling the endpoint without
EXECUTE ANY EXTERNAL ENDPOINT, or without enabling it on SQL Server 2025 - Sending entire tables as context instead of the most relevant chunks
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
- sp_invoke_external_rest_endpoint, Microsoft Learn
- Design and implement RAG with SQL (training module), Microsoft Learn
- Intelligent applications and AI, Microsoft Learn
- FOR JSON (Transact-SQL), Microsoft Learn
- JSON_VALUE (Transact-SQL), Microsoft Learn