Tag: Microsoft Certification

AI, Artificial Intelligence (AI), Azure AI, Databases, DP-800, Microsoft Certification August 1, 2026

Evaluate performance of vector and hybrid search (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement intelligent search
      --> Evaluate performance of vector and hybrid search

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Evaluating the performance of vector and hybrid search solutions is a critical responsibility when developing AI-enabled database applications. While implementing vector search is important, ensuring that the search solution consistently returns accurate, relevant, fast, and scalable results is equally essential. Database developers must understand how to measure search quality, optimize retrieval performance, balance latency with accuracy, and monitor search systems over time.

This knowledge is especially important for applications such as:

Retrieval-Augmented Generation (RAG)
Enterprise knowledge search
AI-powered chatbots
Document retrieval
Recommendation systems
Intelligent search applications

Why Performance Evaluation Matters

Unlike traditional SQL queries that typically return deterministic results, vector and hybrid search systems retrieve documents based on statistical similarity.

This means there is always a balance between:

Search speed
Search accuracy
Resource consumption
Scalability

A search system that responds instantly but returns irrelevant documents is not useful.

Likewise, a system that returns perfect results but requires several seconds per query may not satisfy user expectations.

The goal is to optimize the entire search experience.

Key Performance Metrics

Several metrics are commonly used to evaluate vector and hybrid search.

Query Latency

Latency measures how long a search takes to return results.

Example:

			
User Query
↓
120 ms
↓
Results Returned

		

Lower latency improves user experience.

Typical enterprise AI search systems aim for response times measured in milliseconds.

Factors affecting latency include:

Index type
Dataset size
Hardware resources
Number of search algorithms executed
Network latency
Number of retrieved documents

Throughput

Throughput measures the number of search requests a system can process within a given time.

Examples:

Searches per second
Queries per minute

Higher throughput enables more concurrent users.

Throughput depends on:

CPU
Memory
Index efficiency
Parallel processing
Database architecture

Recall

Recall measures how many relevant documents are successfully retrieved.

Example:

Relevant documents:

			
A
B
C
D
E

		

Returned documents:

			
A
B
C
X
Y

		

Recall:

3 / 5 = 60%

Higher recall generally improves RAG quality because more relevant information is available to the large language model.

Precision

Precision measures how many returned documents are actually relevant.

Example:

Returned:

			
A
B
C
X
Y

		

Relevant:

			
A
B
C

Precision:

3 / 5 = 60%

High precision reduces irrelevant search results.

F1 Score

The F1 Score combines precision and recall into a single metric.

It is especially useful when both false positives and false negatives matter.

Higher F1 scores indicate a better overall balance between retrieving relevant documents and avoiding irrelevant ones.

Mean Reciprocal Rank (MRR)

MRR measures how highly the first relevant result appears in the ranked list.

Example:

Relevant document positions:

Query	First Relevant Result
Query 1	Rank 1
Query 2	Rank 2
Query 3	Rank 4

Higher MRR indicates users find relevant information more quickly.

MRR is commonly used when evaluating question-answering systems and RAG applications.

Normalized Discounted Cumulative Gain (NDCG)

NDCG measures:

Ranking quality
Position of relevant documents
Graded relevance

Unlike recall, NDCG rewards placing the most relevant documents near the top.

This is especially important because users rarely read beyond the first few search results.

Evaluating Vector Search

When evaluating vector search, developers typically measure:

Recall
Precision
Latency
Index build time
Memory usage
Storage requirements

Index Performance

Questions include:

How quickly are searches completed?
How much memory does the index require?
How long does index creation take?
How efficiently are inserts handled?

Search Quality

Evaluate:

Are similar documents retrieved?
Are unrelated documents excluded?
Are synonyms recognized?
Does semantic similarity match user expectations?

Evaluating Hybrid Search

Hybrid search combines:

Full-text search
Vector search
Metadata filtering
Ranking algorithms such as Reciprocal Rank Fusion (RRF)
Optional semantic reranking

Because more components participate, additional evaluation is necessary.

Ranking Quality

Developers evaluate whether:

Exact matches appear near the top.
Semantically relevant documents are included.
Duplicate results are minimized.
Ranking is consistent.

Hybrid Relevance

Example query:

“Reduce Azure costs”

Good hybrid results may include:

Azure cost optimization
Cloud spending reduction
Budget management
Reserved capacity guidance

Poor hybrid results may include unrelated Azure topics.

Measuring Retrieval Quality

Many organizations create benchmark datasets.

Example:

Question:

“How do I configure VPN access?”

Expected documents:

VPN Setup Guide
Remote Access Policy
Authentication Configuration

The search system is evaluated based on whether these expected documents appear in the returned results.

Human Evaluation

Automated metrics cannot evaluate every aspect of search quality.

Organizations often perform manual reviews.

Experts examine:

Relevance
Completeness
Ranking quality
Consistency

Human evaluation is particularly valuable for RAG applications.

Offline Evaluation

Offline testing uses historical datasets.

Advantages:

Repeatable
Safe
Fast
No production impact

Developers compare:

Multiple embedding models
Index types
Similarity metrics
Ranking algorithms

Online Evaluation

Online evaluation uses live users.

Common techniques include:

A/B Testing

Group A:

Current search system

Group B:

New search implementation

Metrics compared include:

Click-through rate
User satisfaction
Search success
Session completion

User Feedback

Collect feedback such as:

Helpful
Not Helpful

User feedback helps improve future search tuning.

Factors Affecting Vector Search Performance

Embedding Quality

Poor embeddings reduce retrieval quality regardless of index performance.

Always choose embedding models appropriate for the domain.

Similarity Metric

Common choices:

Cosine similarity
Dot product
Euclidean distance

Using the wrong metric can reduce search accuracy.

Vector Index Type

Different index types provide different tradeoffs.

Index	Speed	Recall	Memory
Flat	Slow	Highest	Moderate
HNSW	Very Fast	Very High	High
IVF	Fast	High	Moderate
IVF + PQ	Very Fast	Moderate-High	Low

Candidate Set Size

Returning more candidate documents often increases recall.

However:

Latency increases.
More data must be reranked.
LLM token usage increases in RAG.

Balance is important.

Metadata Filtering

Filtering improves:

Precision
Latency

Example:

WHERE Department = 'Finance'

Searching fewer documents reduces processing time while improving relevance.

Evaluating Hybrid Search Components

Keyword Search

Evaluate:

Exact matches
Phrase matching
Synonym handling
Technical terminology

Vector Search

Evaluate:

Semantic understanding
Related concepts
Context awareness

Reciprocal Rank Fusion (RRF)

Evaluate:

Ranking consistency
Combined relevance
Candidate diversity

Semantic Reranking

Evaluate:

Final ranking quality
User satisfaction
Response accuracy

Common Performance Bottlenecks

Missing Vector Index

Searching every embedding significantly increases latency.

Poor Embeddings

Weak embeddings reduce semantic quality.

Excessive Candidate Retrieval

Retrieving hundreds of documents unnecessarily increases reranking and LLM processing time.

Large Embedding Dimensions

Higher-dimensional embeddings require:

More storage
More memory
More computation

Frequent Index Rebuilds

Rebuilding indexes too frequently can consume unnecessary resources.

Use incremental updates where supported.

Optimization Techniques

Choose the Correct Index

Examples:

Small datasets → Flat
Medium datasets → HNSW
Very large datasets → IVF or IVF + PQ

Tune Candidate Count

Retrieve only the number of documents needed.

Use Metadata Filters

Reduce unnecessary searches.

Optimize Embeddings

Select high-quality embedding models.

Use Hybrid Search

Combining lexical and semantic search generally improves relevance.

Apply Semantic Reranking

Use reranking on a limited candidate set to improve final result quality.

Performance Monitoring

Production systems should monitor:

Average latency
Peak latency
Recall
Precision
Throughput
Memory usage
Index size
Search failures
User satisfaction
Search abandonment rate

Monitoring enables proactive tuning as data volumes and usage patterns evolve.

Best Practices

Benchmark search quality before deployment.
Measure both latency and retrieval quality.
Use benchmark datasets with known expected results.
Combine automated metrics with human evaluation.
Tune candidate retrieval size based on workload.
Select the appropriate vector index for dataset size.
Monitor production search metrics continuously.
Refresh embeddings when source data changes significantly.
Evaluate hybrid search using realistic business queries.
Test changes in a staging environment before production deployment.

DP-800 Exam Tips

Remember these key points for the exam:

Vector search performance should be evaluated using both speed and retrieval quality metrics.
Recall measures how many relevant documents are retrieved.
Precision measures how many returned documents are relevant.
MRR evaluates how quickly users encounter the first relevant result.
NDCG evaluates the quality of document ranking.
Hybrid search should be evaluated as a complete pipeline, including keyword search, vector search, RRF, and optional semantic reranking.
Metadata filtering improves both precision and performance.
Human evaluation remains important because automated metrics cannot fully measure search usefulness.
Production systems should continuously monitor latency, recall, throughput, and user satisfaction.

Practice Exam Questions

Question 1

A database developer is evaluating a vector search solution. Which metric measures the percentage of retrieved documents that are actually relevant?

A. Recall

B. Latency

C. Precision

D. Throughput

Answer: C

Explanation:
Precision measures the proportion of retrieved documents that are relevant. High precision indicates that the search results contain few irrelevant documents.

Question 2

A Retrieval-Augmented Generation (RAG) application consistently retrieves only three of the five relevant documents for most user queries.

Which performance metric is primarily affected?

A. Recall

B. Mean Reciprocal Rank (MRR)

C. Throughput

D. Query latency

Answer: A

Explanation:
Recall measures how many relevant documents are successfully retrieved. Missing relevant documents lowers the recall score.

Question 3

Which metric evaluates how quickly users encounter the first relevant search result?

A. F1 Score

B. Mean Reciprocal Rank (MRR)

C. Precision

D. Throughput

Answer: B

Explanation:
MRR evaluates the ranking position of the first relevant result, rewarding systems that place useful documents near the top of the results list.

Question 4

A search solution returns highly relevant documents, but users complain that responses take several seconds.

Which performance metric should the development team investigate first?

A. Index build time

B. Storage utilization

C. Embedding dimension

D. Query latency

Answer: D

Explanation:
Query latency measures the time required to return search results. High latency negatively impacts the user experience, even when retrieval quality is good.

Question 5

Which statement best describes hybrid search performance evaluation?

A. Only vector search accuracy needs to be measured.

B. Only keyword search latency matters.

C. Evaluation should include keyword search, vector search, ranking quality, and overall retrieval performance.

D. Performance is determined solely by embedding size.

Answer: C

Explanation:
Hybrid search combines multiple retrieval methods, so developers should evaluate the complete search pipeline rather than a single component.

Question 6

A developer increases the number of candidate documents retrieved before semantic reranking.

What is the most likely tradeoff?

A. Lower latency and reduced memory usage

B. Higher recall but increased latency and reranking costs

C. Reduced recall with faster indexing

D. Elimination of vector indexing requirements

Answer: B

Explanation:
Retrieving more candidate documents increases the likelihood of finding relevant information but also increases processing time, reranking effort, and LLM token usage.

Question 7

Why is human evaluation still valuable when assessing AI-powered search systems?

A. Automated metrics cannot fully measure user relevance and usefulness.

B. Human evaluation eliminates the need for benchmark datasets.

C. Human reviewers create vector indexes.

D. Human evaluation replaces latency testing.

Answer: A

Explanation:
While automated metrics quantify retrieval quality, human reviewers can assess contextual relevance, completeness, and overall usefulness from a user perspective.

Question 8

Which optimization technique can improve both search precision and query performance?

A. Increasing embedding dimensions indefinitely

B. Removing vector indexes

C. Using metadata filtering to narrow the search scope

D. Returning every matching document

Answer: C

Explanation:
Metadata filters reduce the number of candidate documents that must be searched, improving both relevance and performance.

Question 9

Which performance metric evaluates the overall quality of document ranking by giving more credit when highly relevant documents appear near the top of the results?

A. Recall

B. Precision

C. Throughput

D. Normalized Discounted Cumulative Gain (NDCG)

Answer: D

Explanation:
NDCG measures ranking quality by considering both document relevance and the position of documents in the ranked results, rewarding systems that place the most relevant items first.

Question 10

A development team wants to compare two different embedding models before deploying a new search solution.

Which evaluation approach is most appropriate?

A. Online A/B testing only

B. Disable benchmarking and rely on production feedback

C. Conduct repeatable offline testing using benchmark datasets with expected search results

D. Measure only CPU utilization

Answer: C

Explanation:
Offline benchmarking with known datasets enables developers to compare embedding models, similarity metrics, and indexing strategies safely and consistently before deploying changes to production.

Go to the DP-800 Exam Prep Hub main page

AI, Artificial Intelligence (AI), Azure AI, Databases, DP-800, Microsoft Certification August 1, 2026

Identify use cases for RAG (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement retrieval-augmented generation (RAG)
      --> Identify use cases for RAG

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Retrieval-Augmented Generation (RAG) is one of the most important architectural patterns in modern AI-enabled database applications. Rather than relying solely on the knowledge contained within a Large Language Model (LLM), RAG retrieves relevant information from trusted data sources at query time and supplies that information to the model before it generates a response.

For the DP-800 exam, you should understand when RAG is appropriate, which business problems it solves, its advantages and limitations, and the types of applications that benefit most from its use.

What Is Retrieval-Augmented Generation (RAG)?

Retrieval-Augmented Generation (RAG) is an AI architecture that combines:

Information retrieval
Vector search
Large Language Models (LLMs)

Instead of asking an LLM to answer a question solely from its training data, a RAG system first retrieves relevant information from a database, document repository, or knowledge base.

The retrieved information is then included in the prompt sent to the LLM.

The workflow looks like this:

			
User Question
      │
      ▼
Generate Query Embedding
      │
      ▼
Vector or Hybrid Search
      │
      ▼
Retrieve Relevant Documents
      │
      ▼
Build Prompt with Retrieved Context
      │
      ▼
Large Language Model
      │
      ▼
Grounded Response

		

This process enables the model to answer using current, organization-specific, and trusted information.

Why RAG Is Needed

LLMs have several limitations when used independently.

These include:

Knowledge is limited to training data.
Information may become outdated.
Models cannot automatically access private organizational data.
Responses may contain hallucinations (confident but incorrect information).

RAG addresses these limitations by retrieving external information before response generation.

For example:

Without RAG:

“What is our company’s parental leave policy?”

The LLM has no knowledge of an organization’s private HR documents.

With RAG:

The system retrieves the latest HR policy document and provides it to the LLM, enabling it to generate an accurate, grounded response.

When Should You Use RAG?

RAG is most valuable when answers depend on information that is:

Frequently updated
Organization-specific
Too large to include in prompts directly
Stored in databases or documents
Required to be accurate and traceable

Typical sources include:

SQL databases
Knowledge bases
PDFs
SharePoint libraries
Wikis
Product documentation
Policies
Support articles
Contracts
Technical manuals

Common Business Use Cases

1. Enterprise Knowledge Management

One of the most common RAG implementations is an internal knowledge assistant.

Employees can ask questions such as:

“How do I request family medical leave?”

The system retrieves HR documentation and generates a conversational answer.

Benefits include:

Faster information access
Reduced HR workload
Consistent answers
Always uses the latest documents

2. Customer Support

Support organizations often maintain thousands of troubleshooting articles.

Example question:

“Why won’t my VPN connect?”

Instead of requiring agents to manually search documentation, RAG retrieves relevant articles and generates a summarized answer.

Benefits:

Faster issue resolution
Improved customer satisfaction
Reduced training requirements
Consistent troubleshooting guidance

3. Technical Documentation Assistants

Software vendors publish extensive documentation.

Example:

“How do I configure Transparent Data Encryption?”

RAG retrieves:

Product documentation
Configuration guides
Best practices

The LLM produces a concise explanation grounded in the documentation.

4. SQL Database Assistants

Database developers may ask:

Explain this stored procedure.
Which table stores customer addresses?
Show the indexing strategy.
What permissions exist on this database?

A RAG system retrieves schema information, documentation, and metadata before generating responses.

5. Help Desk Automation

IT departments frequently answer repetitive questions.

Examples:

Password resets
VPN setup
Printer installation
Software installation
MFA enrollment

RAG enables intelligent self-service portals.

6. Legal Research

Law firms manage:

Contracts
Regulations
Case law
Internal legal guidance

RAG retrieves relevant documents before generating summaries.

Benefits:

Faster legal research
Improved consistency
Reduced manual searching

7. Healthcare Knowledge Systems

Healthcare organizations maintain:

Clinical guidelines
Treatment protocols
Internal procedures

RAG retrieves the latest guidance to support clinicians while ensuring answers are based on approved information.

8. Financial Services

Financial institutions use RAG for:

Compliance documentation
Regulatory guidance
Investment research
Internal policies

Because regulations change frequently, RAG provides more current information than relying solely on a model’s training data.

9. Product Recommendation Systems

Instead of searching manually through product catalogs:

Customer asks:

“I’m looking for a waterproof hiking backpack.”

RAG retrieves product specifications before the LLM generates recommendations.

10. Research Assistants

Researchers query:

Scientific papers
Internal reports
Publications
Technical documents

RAG retrieves relevant documents and summarizes findings.

Industry Examples

Industry	Example RAG Use Case
Healthcare	Clinical guideline assistant
Banking	Regulatory compliance assistant
Insurance	Policy document assistant
Manufacturing	Equipment maintenance assistant
Retail	Product recommendation assistant
Education	Course material assistant
Government	Citizen information portal
Legal	Contract and legal research assistant
Technology	Documentation chatbot
Human Resources	Employee policy assistant

When RAG Is NOT Necessary

RAG is not the best solution for every AI application.

Examples where RAG may not be required include:

Creative writing
Brainstorming ideas
Poetry generation
Fiction writing
General conversations
Language translation
Grammar correction

These tasks rely primarily on the language capabilities of the LLM rather than external knowledge.

RAG vs Fine-Tuning

A common exam topic is distinguishing RAG from fine-tuning.

RAG	Fine-Tuning
Retrieves external information	Modifies model weights
Uses current data	Learns from training data
No retraining required for document updates	Requires retraining for new knowledge
Best for dynamic information	Best for changing model behavior
Uses databases and documents	Uses training datasets

Example:

Company updates its vacation policy.

With RAG:

Simply update the knowledge base.

With fine-tuning:

The model would need to be retrained to incorporate the new policy.

Benefits of RAG

Current Information

Answers reflect the latest available documents.

Reduced Hallucinations

The LLM is grounded with trusted information before generating responses.

Organization-Specific Knowledge

Private business data remains outside the foundation model and is retrieved only when needed.

No Model Retraining

Updating documents updates the knowledge available to the system.

Better Accuracy

Responses are based on authoritative content rather than the model’s memory.

Explainability

Many RAG systems cite or link to the documents used to generate responses.

Limitations of RAG

Dependent on Retrieval Quality

Poor retrieval leads to poor responses.

Requires Search Infrastructure

Organizations must maintain:

Embeddings
Vector indexes
Search indexes
Metadata
Documents

Additional Latency

Searching for documents adds time before the LLM generates a response.

Token Limits

Too many retrieved documents may exceed the LLM’s context window.

Systems typically retrieve only the most relevant documents.

Selecting Good RAG Use Cases

Ideal RAG scenarios include:

Large document collections
Frequently changing information
Private organizational knowledge
Regulatory documentation
Technical documentation
Search-heavy workloads
Question-answering systems

Less suitable scenarios include:

Pure text generation
Entertainment applications
Creative storytelling
Static knowledge with no need for external sources

RAG in SQL-Based AI Solutions

Modern SQL platforms increasingly support capabilities that enable RAG solutions, including:

Vector data types
Embedding storage
Vector indexes
Hybrid search
Similarity search
Integration with Azure AI services
Secure access to structured and unstructured enterprise data

This allows developers to build AI applications that combine relational data with semantic search in a single solution.

Best Practices

Use RAG for applications requiring current or organization-specific information.
Build high-quality vector indexes and embeddings to improve retrieval accuracy.
Combine vector search with keyword search using hybrid search when appropriate.
Retrieve only the most relevant documents to stay within LLM context limits.
Apply security trimming so users retrieve only documents they are authorized to access.
Regularly update embeddings and indexes when source content changes.
Monitor retrieval quality using metrics such as precision, recall, and user feedback.
Include citations or source references whenever possible to increase trust.

DP-800 Exam Tips

Remember these key points for the exam:

RAG retrieves external information before the LLM generates a response.
RAG is ideal for organization-specific, frequently changing, or private knowledge.
RAG reduces hallucinations by grounding responses in retrieved documents.
RAG is commonly used with vector search and hybrid search.
RAG differs from fine-tuning because it does not modify the model’s weights.
Updating a knowledge base is typically sufficient to provide new information to a RAG system.
Common RAG use cases include enterprise search, customer support, technical documentation, compliance, and knowledge management.
Strong retrieval quality is essential because poor retrieval leads to poor AI responses.

Practice Exam Questions

Question 1

A company wants an AI assistant that answers employee questions using the latest HR policies stored in an internal document repository.

Which AI architecture is the most appropriate?

A. Fine-tune a language model every time a policy changes.

B. Use Retrieval-Augmented Generation (RAG).

C. Train a new embedding model monthly.

D. Use only keyword search without an LLM.

Answer: B

Explanation:
RAG retrieves the latest HR documents at query time and provides them to the LLM, allowing responses to reflect current policies without retraining the model.

Question 2

Which scenario is the best candidate for implementing a RAG solution?

A. Generating original poetry

B. Creating fictional stories

C. Answering questions using frequently updated product documentation

D. Producing creative marketing slogans

Answer: C

Explanation:
RAG excels when responses depend on current, external, or organization-specific information, such as product documentation that changes over time.

Question 3

Why does RAG generally reduce hallucinations compared to using an LLM alone?

A. It increases the model’s parameter count.

B. It permanently stores retrieved documents inside the model.

C. It grounds responses using relevant retrieved information.

D. It eliminates vector search.

Answer: C

Explanation:
By providing the LLM with relevant documents before response generation, RAG enables the model to base its answers on trusted information instead of relying solely on its training data.

Question 4

A legal firm needs an AI assistant that answers questions using thousands of contracts and regulatory documents that change regularly.

Which solution is most appropriate?

A. Static prompting only

B. Fine-tuning only

C. Rule-based automation

D. Retrieval-Augmented Generation (RAG)

Answer: D

Explanation:
RAG is well suited for dynamic document collections because updated documents become available to the AI system without requiring model retraining.

Question 5

Which statement correctly distinguishes RAG from fine-tuning?

A. RAG modifies the model’s internal weights.

B. Fine-tuning retrieves external documents during every query.

C. RAG retrieves external information at query time, while fine-tuning changes the model through additional training.

D. There is no practical difference between the two approaches.

Answer: C

Explanation:
RAG supplements a model with retrieved context, whereas fine-tuning changes the model’s learned behavior through additional training.

Question 6

A company updates its employee handbook every month.

What is typically required for a RAG solution to use the latest information?

A. Retrain the large language model.

B. Replace the vector database.

C. Update the document repository, regenerate embeddings if needed, and refresh the search index.

D. Reinstall the AI application.

Answer: C

Explanation:
RAG systems rely on current indexed content. When documents change, embeddings and indexes should be refreshed so the retrieval system can locate the updated information.

Question 7

Which use case is generally least appropriate for a RAG implementation?

A. Internal IT help desk assistant

B. Regulatory compliance assistant

C. Technical documentation chatbot

D. Creative short story generation

Answer: D

Explanation:
Creative writing tasks primarily depend on the language generation capabilities of the model and typically do not require retrieval from external knowledge sources.

Question 8

A financial institution wants an AI solution that always references the latest compliance documents before answering user questions.

What is the primary advantage of using RAG?

A. It permanently stores compliance documents inside the LLM.

B. It enables responses based on current external documents without retraining the model.

C. It eliminates the need for search indexes.

D. It automatically fine-tunes the LLM after every document update.

Answer: B

Explanation:
RAG retrieves current compliance documentation during each query, ensuring responses reflect the latest available information while avoiding repeated model retraining.

Question 9

Which technology is most commonly paired with RAG to retrieve semantically relevant documents?

A. Primary key indexes

B. Trigger-based replication

C. Vector search

D. Transaction log backups

Answer: C

Explanation:
Vector search retrieves semantically similar documents using embeddings and is a foundational component of most modern RAG implementations.

Question 10

A database developer is evaluating potential AI projects.

Which project would benefit the most from a RAG architecture?

A. A calculator that performs arithmetic operations

B. A chatbot that answers questions using an organization’s internal SQL documentation and knowledge base

C. A utility that formats SQL code

D. A script that generates random passwords

Answer: B

Explanation:
A chatbot that relies on organization-specific documentation is an ideal RAG use case because it requires access to current, trusted knowledge that is not contained within the LLM’s training data.

Go to the DP-800 Exam Prep Hub main page

AI, Artificial Intelligence (AI), Databases, DP-800, Microsoft Certification August 1, 2026

Create a prompt by using the sp_invoke_external_rest_endpoint stored procedure (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement retrieval-augmented generation (RAG)
      --> Identify use cases for RAG

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

As SQL databases increasingly integrate with AI services, developers can call external REST APIs directly from Transact-SQL (T-SQL). One important capability is the sp_invoke_external_rest_endpoint system stored procedure, which enables SQL code to securely invoke REST endpoints, including AI services such as Azure AI Foundry models, Azure OpenAI Service, and other HTTP-based APIs.

For the DP-800 exam, you should understand how to use this stored procedure to build prompts, send requests to AI models, process responses, and incorporate Retrieval-Augmented Generation (RAG) workflows into SQL applications.

What Is `sp_invoke_external_rest_endpoint`?

sp_invoke_external_rest_endpoint is a system stored procedure that enables T-SQL code to invoke external REST APIs directly from supported SQL platforms, such as Azure SQL Database and SQL Server 2025 (where supported and configured).

Instead of requiring an application layer to call an external AI service, the database itself can send an HTTPS request and receive the response.

Typical workflow:

			
T-SQL
    │
    ▼
sp_invoke_external_rest_endpoint
    │
    ▼
REST API
    │
    ▼
AI Model
    │
    ▼
JSON Response
    │
    ▼
SQL Processing

		

This capability allows SQL applications to integrate directly with AI-powered services while keeping business logic close to the data.

Why Use `sp_invoke_external_rest_endpoint`?

Many AI services expose REST APIs.

Examples include:

Azure OpenAI
Azure AI Foundry models
Azure AI Language
Azure AI Translator
Azure AI Vision
Custom REST APIs
Internal enterprise AI services

Using sp_invoke_external_rest_endpoint enables SQL developers to:

Generate AI responses
Summarize database content
Perform sentiment analysis
Translate text
Classify documents
Invoke Retrieval-Augmented Generation (RAG) workflows
Call custom enterprise AI services

Role in Retrieval-Augmented Generation (RAG)

In a RAG solution, the database typically performs several tasks:

Retrieve relevant documents.
Build the prompt.
Call the LLM.
Return the grounded response.

Example workflow:

			
User Question
      │
      ▼
Vector Search
      │
      ▼
Retrieve Context
      │
      ▼
Build Prompt
      │
      ▼
sp_invoke_external_rest_endpoint
      │
      ▼
Large Language Model
      │
      ▼
Grounded Answer

		

The stored procedure serves as the bridge between SQL and the external AI model.

Components of an AI Request

A typical AI request contains several elements.

Endpoint URL

The REST endpoint specifies where the request is sent.

Examples include:

Azure OpenAI endpoint
Azure AI Foundry endpoint
Internal REST API

HTTP Method

Most AI inference requests use:

POST

because prompt data is sent in the request body.

HTTP Headers

Headers commonly include:

Authorization
Content-Type
API version (when required)
Subscription key (for applicable services)

Example:

			
Content-Type: application/json
Authorization: Bearer <token>

Authentication methods vary by service and may use Microsoft Entra ID (formerly Azure Active Directory), managed identities, or API keys.

JSON Request Body

The request body contains:

Prompt
System instructions
User input
Generation parameters

Example:

			
{
  "messages": [
    {
      "role": "system",
      "content": "You are a SQL assistant."
    },
    {
      "role": "user",
      "content": "Explain clustered indexes."
    }
  ]
}

		

The exact JSON schema depends on the AI service being called.

Creating Effective Prompts

Prompt engineering significantly affects AI output quality.

A good prompt should include:

Clear instructions
Business context
Retrieved documents (for RAG)
User question
Expected output format

Example Prompt Structure

			
System:
You are an expert SQL assistant.
Context:
<Document retrieved from vector search>
Question:
How do clustered indexes improve performance?
Instructions:
Answer only using the supplied context.

		

This structure helps reduce hallucinations and produces grounded responses.

Building Prompts in SQL

Developers often assemble prompts dynamically using T-SQL variables.

Conceptually:

			
DECLARE @Context NVARCHAR(MAX);
DECLARE @Question NVARCHAR(MAX);
SET @Context =
'Clustered indexes store table rows in key order...';
SET @Question =
'Explain clustered indexes.';

		

The prompt can then be incorporated into the JSON request body before invoking the external endpoint.

Note: The exact JSON construction depends on the target AI service’s REST API.

Example Workflow

A simplified workflow is:

			
Retrieve Documents
↓
Build Prompt
↓
Create JSON Payload
↓
Call REST Endpoint
↓
Receive JSON Response
↓
Extract Generated Answer

		

Conceptual Example

The following simplified example illustrates the overall flow. It is not intended to represent every required parameter or authentication option.

			
EXEC sp_invoke_external_rest_endpoint
    @method = 'POST',
    @url = 'https://<ai-endpoint>',
    @headers = '{"Content-Type":"application/json"}',
    @payload = '{"messages":[...]}';

		

The supported parameters, authentication methods, and payload format depend on the SQL platform and the REST API being invoked.

Using Retrieved Context in RAG

Suppose vector search returns:

Document:

Clustered indexes physically organize rows according to the index key.

User asks:

Why are clustered indexes faster?

Prompt:

			
Use only the following information:
Clustered indexes physically organize rows according to the index key.
Question:
Why are clustered indexes faster?

This grounded prompt improves response accuracy.

Processing the Response

Most AI services return JSON.

Example (simplified):

			
{
  "choices": [
    {
      "message": {
        "content": "Clustered indexes improve performance because..."
      }
    }
  ]
}

		

SQL applications can use JSON functions such as:

OPENJSON
JSON_VALUE
JSON_QUERY

to extract values from the response.

Example:

			
SELECT JSON_VALUE(@Response,
'$.choices[0].message.content');

Authentication Considerations

REST endpoints must be secured.

Depending on the service, authentication may use:

Microsoft Entra ID
Managed Identity
API Keys
OAuth access tokens

Developers should avoid embedding secrets directly in T-SQL code.

Instead, use secure credential management mechanisms supported by the platform.

Error Handling

Common failures include:

Authentication Errors

Examples:

Invalid token
Expired credentials
Missing permissions

Network Errors

Examples:

Endpoint unavailable
Timeout
DNS failures

Invalid Request

Examples:

Incorrect JSON
Unsupported parameter
Missing required fields

Rate Limiting

Many AI services enforce request limits.

Applications should be designed to handle HTTP responses such as:

429 Too Many Requests

using retry logic with exponential backoff where appropriate.

Performance Considerations

Calling external AI services introduces additional latency.

Factors include:

Network communication
AI inference time
Prompt size
Response size
Concurrent requests

Large prompts increase:

Token usage
Response time
Cost

Developers should include only the most relevant retrieved context.

Security Best Practices

When invoking external AI services:

Use HTTPS endpoints.
Authenticate securely using supported identity mechanisms.
Protect API credentials.
Validate user input before constructing prompts.
Avoid exposing confidential information unnecessarily.
Apply least-privilege access.
Monitor outbound API usage.
Log failures for troubleshooting while avoiding logging sensitive prompt content.

Best Practices for Prompt Design

Provide clear system instructions.
Include only relevant retrieved context.
Tell the model to answer using the supplied context.
Specify the desired output format.
Keep prompts concise to reduce latency and token consumption.
Remove duplicate or irrelevant information.
Test prompts using realistic business questions.
Evaluate responses for accuracy and consistency.

Common RAG Prompt Pattern

A common prompt template includes:

			
System
↓
Instructions
↓
Retrieved Context
↓
User Question
↓
Expected Response Format

		

This structure helps produce consistent, grounded responses.

DP-800 Exam Tips

Remember these key points for the exam:

sp_invoke_external_rest_endpoint enables T-SQL code to call external REST APIs directly.
In RAG solutions, the stored procedure is commonly used after retrieving relevant documents and constructing a grounded prompt.
AI requests typically use the HTTP POST method with a JSON payload.
Prompt quality directly influences response quality.
Include retrieved context to reduce hallucinations.
Responses from AI services are typically returned as JSON and can be parsed using SQL JSON functions.
Secure authentication is essential; avoid hard-coding credentials.
Minimize prompt size to improve performance and reduce token costs.

Practice Exam Questions

Question 1

A developer wants to call an Azure AI model directly from T-SQL without writing application code.

Which SQL capability enables this functionality?

A. sp_execute_external_script

B. OPENROWSET

C. sp_invoke_external_rest_endpoint

D. BULK INSERT

Answer: C

Explanation:
sp_invoke_external_rest_endpoint enables supported SQL platforms to invoke external REST APIs directly from T-SQL, making it suitable for integrating AI services.

Question 2

In a Retrieval-Augmented Generation (RAG) solution, when is sp_invoke_external_rest_endpoint typically called?

A. Before documents are retrieved.

B. After relevant context has been retrieved and incorporated into the prompt.

C. Before embeddings are generated.

D. Before the vector index is created.

Answer: B

Explanation:
In a typical RAG workflow, relevant documents are first retrieved using vector or hybrid search. The retrieved context is then included in the prompt before calling the LLM through the REST endpoint.

Question 3

Which HTTP method is most commonly used when invoking an AI chat completion REST endpoint?

A. GET

B. DELETE

C. PUT

D. POST

Answer: D

Explanation:
AI inference requests generally send prompts and parameters within the request body, making POST the standard HTTP method.

Question 4

What is the primary benefit of including retrieved documents in the prompt sent to an AI model?

A. It permanently trains the language model.

B. It reduces network latency.

C. It grounds the response using relevant information.

D. It compresses the prompt.

Answer: C

Explanation:
Including retrieved context allows the model to generate responses based on trusted information, improving accuracy and reducing hallucinations.

Question 5

Which SQL functionality is commonly used to extract generated text from a JSON response returned by an AI service?

A. JSON_VALUE

B. MERGE

C. PIVOT

D. ROW_NUMBER

Answer: A

Explanation:
Functions such as JSON_VALUE, JSON_QUERY, and OPENJSON enable SQL developers to parse JSON responses returned by REST APIs.

Question 6

A developer is designing prompts for an AI-powered SQL assistant.

Which prompt design practice generally produces the most reliable responses?

A. Include unrelated historical data to provide additional context.

B. Keep prompts vague so the model has more flexibility.

C. Provide clear instructions and include only relevant retrieved context.

D. Omit the user’s question whenever possible.

Answer: C

Explanation:
Clear instructions and focused, relevant context help the model generate accurate, grounded, and consistent responses.

Question 7

Which authentication approach is recommended when calling secured AI REST endpoints from SQL?

A. Store API keys directly in every stored procedure.

B. Use supported secure authentication mechanisms such as Microsoft Entra ID or managed identities where available.

C. Disable authentication during development and production.

D. Send credentials as query-string parameters.

Answer: B

Explanation:
Secure authentication methods reduce the risk of credential exposure and align with security best practices for accessing external services.

Question 8

What is a common consequence of including excessive retrieved content in a prompt?

A. Lower token usage.

B. Faster inference times.

C. Reduced storage requirements.

D. Increased latency and higher token consumption.

Answer: D

Explanation:
Longer prompts require more tokens to process, increasing inference time, cost, and the likelihood of exceeding the model’s context window.

Question 9

A database application receives an HTTP 429 response from an AI REST endpoint.

What does this response typically indicate?

A. The JSON response is malformed.

B. Authentication failed.

C. The request exceeded the service’s rate limit.

D. The endpoint only accepts GET requests.

Answer: C

Explanation:
HTTP 429 (“Too Many Requests”) indicates that the client has exceeded the allowed request rate. Applications should implement appropriate retry strategies.

Question 10

Which sequence best represents a typical RAG workflow implemented from SQL?

A. Generate response → Retrieve documents → Build prompt → Parse JSON

B. Retrieve documents → Build prompt → Invoke sp_invoke_external_rest_endpoint → Parse the JSON response

C. Create vector index → Generate embeddings → Train the LLM

D. Build prompt → Delete vector index → Generate embeddings

Answer: B

Explanation:
A typical SQL-based RAG workflow retrieves relevant documents, constructs a grounded prompt, invokes the external AI service using sp_invoke_external_rest_endpoint, and then parses the returned JSON response for use by the application.

Go to the DP-800 Exam Prep Hub main page

AI, Artificial Intelligence (AI), Databases, DP-800, Microsoft Certification August 1, 2026

Convert structured data to JSON for language model processing (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement retrieval-augmented generation (RAG)
      --> Convert structured data to JSON for language model processing

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Modern AI-enabled database applications frequently need to send structured data stored in relational tables to Large Language Models (LLMs). Because LLMs interact with text or structured payloads such as JSON rather than relational tables, developers must transform SQL query results into JSON before sending them to AI services.

For the DP-800 exam, you should understand how to convert relational data into JSON using SQL, why JSON is the preferred interchange format for AI services, how JSON is used in Retrieval-Augmented Generation (RAG) workflows, and the best practices for preparing structured data for language model processing.

Why Convert Structured Data to JSON?

Relational databases organize information into:

Tables
Rows
Columns
Relationships

Large Language Models, however, consume:

Natural language
JSON documents
API payloads
Structured text

JSON (JavaScript Object Notation) provides a lightweight, hierarchical format that is easy for applications, APIs, and AI models to process.

Instead of sending an entire table, developers typically send only the relevant records formatted as JSON.

Role of JSON in AI Applications

JSON serves as the common data exchange format between SQL databases and AI services.

Typical workflow:

			
SQL Database
      │
      ▼
Query Structured Data
      │
      ▼
Convert to JSON
      │
      ▼
Build AI Prompt
      │
      ▼
REST API Request
      │
      ▼
Large Language Model
      │
      ▼
AI Response

		

This process allows structured business data to become part of an AI prompt or API request.

What Is JSON?

JSON is a text-based format consisting of key-value pairs and arrays.

Example:

			
{
  "CustomerID": 1001,
  "CustomerName": "Contoso Ltd.",
  "Country": "USA",
  "CreditLimit": 50000
}

		

Nested objects are also supported.

Example:

			
{
  "OrderID": 1055,
  "Customer": {
      "Name": "Contoso Ltd.",
      "Country": "USA"
  }
}

		

Hierarchical structures like these are easier for language models to interpret than tabular data.

Why AI Models Prefer JSON

JSON provides several advantages:

Human-readable
Machine-readable
Structured
Flexible
Widely supported
Easily serialized
Easily parsed

Most AI REST APIs accept JSON request bodies and return JSON responses.

Converting SQL Query Results to JSON

Modern SQL platforms support generating JSON directly from query results.

For example, SQL Server and Azure SQL Database provide the FOR JSON clause.

Example:

			
SELECT CustomerID,
       CustomerName,
       Country
FROM Customers
FOR JSON AUTO;

		

Sample output:

			
[
  {
    "CustomerID":1001,
    "CustomerName":"Contoso Ltd.",
    "Country":"USA"
  },
  {
    "CustomerID":1002,
    "CustomerName":"Fabrikam",
    "Country":"Canada"
  }
]

		

This JSON can be incorporated into prompts or REST API requests.

FOR JSON AUTO

FOR JSON AUTO automatically generates JSON based on the structure of the SELECT statement.

Advantages:

Minimal configuration
Quick generation
Good for simple queries

Example:

			
SELECT ProductID,
       ProductName,
       Price
FROM Products
FOR JSON AUTO;

		

FOR JSON PATH

FOR JSON PATH provides greater control over the resulting JSON structure.

Example:

			
SELECT
    CustomerID AS 'Customer.ID',
    CustomerName AS 'Customer.Name'
FOR JSON PATH;

Output:

			
[
  {
    "Customer": {
      "ID":1001,
      "Name":"Contoso Ltd."
    }
  }
]

		

FOR JSON PATH is preferred when a specific JSON schema is required by an application or AI service.

Creating Nested JSON

Nested JSON is useful for representing parent-child relationships.

Example:

Customer

↓

Orders

↓

Order Items

Instead of returning multiple unrelated tables, developers can build a hierarchical JSON document that mirrors the business object.

This format is often easier for an LLM to understand.

Using JSON in Prompts

Rather than embedding raw SQL results, developers can include JSON as structured context.

Example prompt:

			
Use the following customer information:
{
  "CustomerID":1001,
  "Name":"Contoso Ltd.",
  "Country":"USA",
  "CreditLimit":50000
}
Summarize the customer's profile.

		

The structured format enables the model to identify fields and values more reliably.

JSON in Retrieval-Augmented Generation (RAG)

In RAG applications, retrieved information often comes from:

SQL queries
Vector search
Hybrid search
APIs

Structured query results can be converted to JSON before being added to the prompt.

Workflow:

			
SQL Query
      │
      ▼
FOR JSON
      │
      ▼
Prompt Construction
      │
      ▼
LLM
      │
      ▼
Grounded Response

		

Combining Structured and Unstructured Data

Many AI applications combine relational data with documents.

Example:

Structured data:

			
{
  "OrderID":1055,
  "Status":"Shipped"
}

Retrieved documentation:

Orders typically arrive within three business days after shipment.

Prompt:

			
Order Information:
{
  "OrderID":1055,
  "Status":"Shipped"
}
Documentation:
Orders typically arrive within three business days.
Answer the customer's question.

		

This approach gives the LLM access to both factual business data and supporting context.

Reducing Token Usage

Large JSON payloads increase:

Prompt size
Latency
API cost
Token consumption

Best practice:

Include only relevant fields.

Instead of:

			
{
  "CustomerID":1001,
  "Name":"Contoso",
  "Country":"USA",
  "Phone":"...",
  "Fax":"...",
  "CreatedDate":"...",
  "LastLogin":"...",
  ...
}

		

Use:

			
{
  "CustomerID":1001,
  "Country":"USA",
  "CreditLimit":50000
}

		

Only include information required to answer the user’s question.

Security Considerations

Before converting SQL data to JSON:

Remove sensitive columns.
Exclude personally identifiable information (PII) unless required and authorized.
Apply row-level security (RLS).
Enforce column-level permissions.
Mask confidential values when appropriate.
Validate user authorization before retrieving data.

AI models should receive only the data necessary to perform the requested task.

Data Quality Considerations

Language model responses are only as good as the input data.

Ensure that:

Missing values are handled appropriately.
Duplicate rows are removed.
Invalid records are excluded.
Data types are consistent.
Field names are meaningful.
JSON is well-formed and valid.

Poor-quality JSON often leads to inaccurate or confusing AI responses.

Processing AI Responses

Most AI services also return JSON.

Example:

			
{
  "summary":
  "Contoso Ltd. is a U.S. customer with a credit limit of $50,000."
}

SQL JSON functions such as:

JSON_VALUE
JSON_QUERY
OPENJSON

can extract values from the response for further processing or storage.

Common Mistakes

Sending Entire Tables

Avoid sending unnecessary rows.

Instead:

Retrieve only relevant records.

Including Too Many Columns

Large prompts increase token usage and cost.

Using Poor Field Names

Prefer:

CustomerName

instead of:

C_Name

Clear field names help improve model understanding.

Ignoring Security

Never expose confidential information unnecessarily.

Creating Invalid JSON

Malformed JSON causes REST API failures and prevents AI services from processing requests.

Best Practices

Use FOR JSON AUTO for simple JSON generation.
Use FOR JSON PATH when custom JSON structures are required.
Return only relevant rows and columns.
Keep JSON concise to reduce token consumption.
Use meaningful field names.
Remove confidential or unnecessary information.
Validate JSON before sending it to AI services.
Combine structured JSON with retrieved documents for RAG scenarios.
Parse AI responses using SQL JSON functions.
Test prompts using realistic business data.

DP-800 Exam Tips

Remember these key points for the exam:

JSON is the standard format for exchanging structured data with AI services.
SQL Server and Azure SQL Database support JSON generation using FOR JSON.
FOR JSON AUTO automatically formats query results.
FOR JSON PATH provides greater control over JSON structure.
RAG solutions often include JSON generated from SQL queries as contextual information.
Smaller, focused JSON payloads reduce token usage and improve performance.
Protect sensitive information before converting data to JSON.
SQL JSON functions can parse AI responses returned as JSON.

Practice Exam Questions

Question 1

A database developer needs to send customer records from SQL Server to a Large Language Model through a REST API.

Which format is most appropriate?

A. XML

B. CSV

C. JSON

D. Binary data

Answer: C

Explanation:
JSON is the standard format accepted by most AI REST APIs because it is lightweight, structured, and easy for both applications and language models to process.

Question 2

Which SQL clause automatically converts query results into JSON using the default structure of the SELECT statement?

A. FOR JSON AUTO

B. FOR XML

C. OPENJSON

D. JSON_VALUE

Answer: A

Explanation:
FOR JSON AUTO automatically generates JSON based on the query structure with minimal configuration.

Question 3

A developer needs complete control over the hierarchy and property names in the generated JSON document.

Which SQL feature should be used?

A. FOR XML

B. FOR JSON PATH

C. JSON_QUERY

D. OPENJSON

Answer: B

Explanation:
FOR JSON PATH allows developers to customize the JSON structure, including nested objects and property names.

Question 4

Why is JSON commonly used when interacting with Large Language Models?

A. It permanently stores embeddings.

B. It replaces vector indexes.

C. It provides a structured, machine-readable format that AI services commonly accept.

D. It automatically encrypts database records.

Answer: C

Explanation:
JSON is widely supported by REST APIs and AI services, making it the preferred format for exchanging structured data.

Question 5

In a Retrieval-Augmented Generation (RAG) solution, why might structured SQL query results be converted to JSON?

A. To include structured business data as context in the prompt sent to the language model.

B. To train the language model.

C. To replace vector embeddings.

D. To eliminate REST APIs.

Answer: A

Explanation:
Structured SQL data converted to JSON can be included in the prompt, allowing the LLM to generate grounded responses using current business information.

Question 6

A developer includes every column from a customer table in the JSON payload, even though only two fields are required.

What is the most likely consequence?

A. Improved retrieval accuracy.

B. Lower API costs.

C. Increased prompt size, token consumption, and latency.

D. Automatic JSON compression.

Answer: C

Explanation:
Sending unnecessary data increases the size of the prompt, which leads to higher token usage, longer response times, and increased cost.

Question 7

Which SQL functions are commonly used to extract values from a JSON response returned by an AI service?

A. ROW_NUMBER and MERGE

B. JSON_VALUE, JSON_QUERY, and OPENJSON

C. PIVOT and UNPIVOT

D. STRING_AGG and GROUP BY

Answer: B

Explanation:
SQL Server provides JSON functions such as JSON_VALUE, JSON_QUERY, and OPENJSON for parsing JSON documents and extracting data.

Question 8

Which practice best improves both security and efficiency when preparing JSON for an AI service?

A. Include every available database column.

B. Return the entire table regardless of the user’s request.

C. Remove unnecessary and sensitive information before generating JSON.

D. Convert the JSON into XML before sending it.

Answer: C

Explanation:
Limiting the JSON payload to only necessary, authorized data reduces token usage, improves performance, and protects sensitive information.

Question 9

What is the primary advantage of using nested JSON structures?

A. They reduce the need for SQL joins.

B. They represent hierarchical relationships in a format that is easier for applications and language models to interpret.

C. They automatically generate embeddings.

D. They eliminate the need for REST APIs.

Answer: B

Explanation:
Nested JSON naturally represents parent-child relationships, making complex business objects easier for both applications and AI models to process.

Question 10

A database application receives a JSON response from an AI service.

What is the next step if the application needs to store the generated summary in a SQL table?

A. Convert the JSON to XML.

B. Rebuild the vector index.

C. Parse the JSON response using SQL JSON functions and extract the required value.

D. Generate new embeddings for the response.

Answer: C

Explanation:
After receiving a JSON response, SQL functions such as JSON_VALUE or OPENJSON can extract the generated content for storage or further processing.

Go to the DP-800 Exam Prep Hub main page

AI, Artificial Intelligence (AI), Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Send results to a language model (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement retrieval-augmented generation (RAG)
      --> Send results to a language model

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

One of the final steps in a Retrieval-Augmented Generation (RAG) workflow is sending retrieved data to a Large Language Model (LLM). After retrieving relevant information from a SQL database, vector index, or hybrid search system, the application packages the data into a prompt and submits it to an AI model through a REST API. The quality of this process directly affects the accuracy, relevance, security, and efficiency of the generated response.

For the DP-800 exam, you should understand how to prepare retrieved results for language model processing, construct effective prompts, submit requests to AI services, handle responses, and follow best practices for security, performance, and reliability.

Where This Step Fits in a RAG Workflow

A Retrieval-Augmented Generation solution consists of several stages.

			
User Question
      │
      ▼
Generate Query Embedding
      │
      ▼
Vector or Hybrid Search
      │
      ▼
Retrieve Relevant Documents
      │
      ▼
Prepare Context
      │
      ▼
Build Prompt
      │
      ▼
Send Request to Language Model
      │
      ▼
Receive AI Response
      │
      ▼
Return Answer to User

		

Sending the retrieved results to the language model is the bridge between the retrieval system and the AI model.

Why Send Retrieved Results?

Large Language Models do not automatically have access to:

SQL databases
Internal documentation
Company policies
Product catalogs
Customer records
Knowledge bases

Instead, developers retrieve the necessary information and include it in the prompt sent to the model.

This process grounds the AI response in trusted, current information.

Components of a Request

A typical request sent to a language model includes several elements.

System Instructions

The system message defines the model’s role and behavior.

Example:

			
You are an expert SQL database assistant.
Answer only using the supplied context.

System instructions establish the rules the model should follow.

Retrieved Context

The retrieved context contains the information found during vector or hybrid search.

Example:

			
Document:
Clustered indexes physically store rows according to the index key.

Only relevant context should be included.

User Question

The original user request is included.

Example:

Why do clustered indexes improve query performance?

The language model combines the retrieved context with the user question to generate an answer.

Output Instructions

Developers may specify:

Response length
Formatting
Tone
JSON output
Markdown output
Bullet lists

Example:

Provide a concise answer in three bullet points.

Preparing Retrieved Results

Retrieved documents often require preprocessing before being sent to the model.

Common preprocessing tasks include:

Removing duplicate documents
Eliminating irrelevant information
Trimming excessively long content
Combining related results
Filtering unauthorized information
Formatting structured data as JSON when appropriate

Proper preparation improves both response quality and efficiency.

Selecting Relevant Context

Sending too much information can reduce answer quality and increase cost.

Best practice:

Retrieve only the top-ranking documents.

For example:

Instead of sending:

50 documents

Send:

Top 3–10 highly relevant documents

The exact number depends on the application’s requirements and the model’s context window.

Structuring the Prompt

A well-organized prompt improves response quality.

A common structure is:

			
System Instructions
Retrieved Context
User Question
Expected Response Format

Example:

			
You are a SQL expert.
Context:
Clustered indexes physically organize table rows according to the key.
Question:
Why do clustered indexes improve query performance?
Answer only using the provided context.

		

Sending Structured Data

Sometimes the retrieved information is relational data rather than documents.

Example SQL output:

Customer	Country	Credit Limit
Contoso	USA	50000

Instead of sending the table directly, developers often convert it to JSON.

Example:

			
{
  "Customer":"Contoso",
  "Country":"USA",
  "CreditLimit":50000
}

		

JSON provides a structured format that AI services process efficiently.

Calling the Language Model

Most AI services expose REST APIs.

A request typically includes:

HTTPS endpoint
HTTP POST method
Authentication
JSON payload

Conceptually:

			
Prompt
↓
JSON Request
↓
REST API
↓
Language Model
↓
JSON Response

		

SQL Server and Azure SQL Database can call supported REST endpoints using the sp_invoke_external_rest_endpoint stored procedure where available.

Processing the Response

Most AI services return JSON.

Example:

			
{
  "choices":[
    {
      "message":{
        "content":"Clustered indexes improve performance because..."
      }
    }
  ]
}

		

SQL applications can extract the generated text using JSON functions such as:

JSON_VALUE
JSON_QUERY
OPENJSON

The application can then display, store, or further process the generated response.

Managing Context Windows

Every language model has a maximum context window.

The context window includes:

System instructions
Retrieved documents
User question
Previous conversation
Generated response

If too much information is included, requests may fail or important information may be truncated.

Developers should:

Remove irrelevant content.
Retrieve fewer documents.
Summarize long documents.
Limit prompt size.

Token Usage

Language models process text as tokens.

Security Considerations

Developers should never send sensitive information unnecessarily.

Examples include:

Passwords
Authentication secrets
Personal identifiers
Confidential financial records
Protected health information
Internal security credentials

Before sending data to an external AI service:

Apply row-level security (RLS).
Apply column-level security.
Remove confidential fields.
Mask sensitive values when appropriate.
Verify user authorization.

Grounding the Response

One of the primary goals of RAG is grounding.

Grounding means that the model bases its answer on retrieved information rather than relying solely on its internal training.

Example instruction:

			
Answer only using the supplied documents.
If the answer is unavailable, say you do not know.

This helps reduce hallucinations.

Handling Errors

Common issues include:

Authentication Failures

Examples:

Expired tokens
Invalid credentials
Missing permissions

Network Problems

Examples:

Endpoint unavailable
Timeouts
DNS failures

Rate Limits

AI services may return:

429 Too Many Requests

Applications should implement retry logic using exponential backoff.

Invalid Requests

Examples:

Malformed JSON
Missing prompt
Unsupported parameters

Performance Considerations

Factors affecting performance include:

Prompt size
Number of retrieved documents
Network latency
AI model size
Token count
Response length
Concurrent requests

Performance can often be improved by:

Sending fewer documents.
Using concise prompts.
Removing duplicate information.
Optimizing retrieval quality.

Common Mistakes

Sending Irrelevant Documents

The language model may generate inaccurate or confusing responses.

Including Entire Database Records

Large prompts increase token usage and cost.

Poor Prompt Design

Ambiguous instructions often produce inconsistent responses.

Ignoring Security

Sensitive information should never be included unless necessary and authorized.

Missing Grounding Instructions

Without guidance, the model may rely on general knowledge instead of retrieved context.

Best Practices

Retrieve only the most relevant documents.
Use clear system instructions.
Include the user’s original question.
Organize prompts consistently.
Limit prompt size to reduce token usage.
Convert structured data to JSON when appropriate.
Remove sensitive information before sending requests.
Validate JSON payloads.
Monitor latency and token consumption.
Evaluate AI responses for accuracy and relevance.

Real-World Example

A company stores warranty information in SQL Server.

Workflow:

Customer asks:”Is my laptop still under warranty?”
SQL retrieves:

			
Product: X500
Purchase Date: January 10, 2025
Warranty: 2 Years

JSON is generated:

			
{
  "Product":"X500",
  "PurchaseDate":"2025-01-10",
  "Warranty":"2 Years"
}

		

Prompt sent to the language model:

			
Use the following warranty information:
{
  "Product":"X500",
  "PurchaseDate":"2025-01-10",
  "Warranty":"2 Years"
}
Answer whether the warranty is still valid.

		

The language model generates a grounded response using the supplied business data.

DP-800 Exam Tips

Remember these key points for the exam:

Sending results to the language model is the final step before AI response generation in a RAG workflow.
Retrieved documents should be relevant, concise, and properly formatted.
System instructions help guide model behavior.
Structured SQL data is often converted to JSON before being included in prompts.
Smaller prompts reduce latency and token costs.
Grounding instructions help reduce hallucinations.
Responses from AI services are typically returned as JSON.
Sensitive information should be removed before sending requests to external AI services.

Practice Exam Questions

Question 1

A developer is building a Retrieval-Augmented Generation (RAG) application.

After retrieving relevant documents from a vector search, what is the next logical step?

A. Send the retrieved context to the language model as part of the prompt.

B. Retrain the language model.

C. Rebuild the vector index.

D. Delete duplicate embeddings.

Answer: A

Explanation:
After retrieval, the relevant documents are incorporated into the prompt and sent to the language model so it can generate a grounded response.

Question 2

Why should retrieved documents be included in a prompt sent to a language model?

A. To permanently update the model’s training data.

B. To ground the model’s response using relevant information.

C. To reduce embedding dimensions.

D. To replace vector indexes.

Answer: B

Explanation:
Including retrieved context enables the model to generate responses based on current, authoritative information rather than relying solely on pre-trained knowledge.

Question 3

Which prompt component defines the behavior the language model should follow?

A. Retrieved context

B. User question

C. System instructions

D. JSON response

Answer: C

Explanation:
System instructions establish the role, behavior, and constraints for the language model, such as answering only from the supplied context.

Question 4

A developer sends fifty retrieved documents to a language model, even though only five are relevant.

What is the most likely consequence?

A. Improved grounding accuracy.

B. Reduced API costs.

C. Faster inference.

D. Increased token usage, latency, and potential reduction in response quality.

Answer: D

Explanation:
Including excessive context increases prompt size, consumes more tokens, raises costs, and may dilute the relevance of the information presented to the model.

Question 5

Which format is commonly used to send structured SQL query results to a language model?

A. Binary

B. XML

C. JSON

D. CSV

Answer: C

Explanation:
JSON is the standard format for exchanging structured data with AI services because it is lightweight, hierarchical, and widely supported.

Question 6

What is the primary purpose of grounding instructions such as “Answer only using the supplied context”?

A. Increase the embedding dimension.

B. Reduce hallucinations by limiting the model to retrieved information.

C. Eliminate authentication requirements.

D. Automatically compress prompts.

Answer: B

Explanation:
Grounding instructions encourage the model to base its responses on the retrieved documents instead of relying on unsupported assumptions or prior training.

Question 7

A language model returns its response as JSON.

Which SQL functions can be used to extract the generated answer?

A. MERGE and GROUP BY

B. ROW_NUMBER and RANK

C. STRING_AGG and PIVOT

D. JSON_VALUE, JSON_QUERY, and OPENJSON

Answer: D

Explanation:
SQL Server provides JSON functions that allow applications to parse AI responses and extract specific values from JSON documents.

Question 8

Which security practice is most appropriate before sending retrieved results to an external AI service?

A. Include every available column to maximize context.

B. Remove sensitive or unauthorized information from the retrieved data.

C. Disable row-level security.

D. Send authentication credentials within the prompt.

Answer: B

Explanation:
Only the information necessary for the AI task should be sent. Sensitive data should be removed or masked, and normal security controls should remain in effect.

Question 9

Why is prompt size an important consideration when sending results to a language model?

A. Larger prompts always improve response quality.

B. Prompt size has no effect on AI services.

C. Larger prompts increase token usage, cost, and response latency.

D. Prompt size determines the embedding algorithm.

Answer: C

Explanation:
Every token contributes to processing time and cost. Keeping prompts concise improves performance while reducing API expenses.

Question 10

A company wants an AI assistant to answer questions using current warranty information stored in SQL Server.

Which approach best supports this requirement?

A. Fine-tune the language model every time warranty records change.

B. Store warranty records directly inside the model.

C. Build a RAG workflow that retrieves the current warranty data, formats it appropriately, and sends it to the language model.

D. Disable retrieval and rely only on the model’s training data.

Answer: C

Explanation:
A RAG solution retrieves current business data at query time, formats it (often as JSON), and sends it to the language model, allowing responses to remain accurate without requiring model retraining.

Go to the DP-800 Exam Prep Hub main page

AI, Artificial Intelligence (AI), Databases, DP-800, Microsoft Certification August 1, 2026

Extract language model responses (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement retrieval-augmented generation (RAG)
      --> Extract language model responses

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

After a Large Language Model (LLM) generates a response, database applications must extract the returned content so it can be displayed to users, stored in the database, or used by downstream processes. Because most AI services return results in JSON format, developers must understand how to parse JSON, extract relevant values, handle errors, validate responses, and integrate the output into SQL-based applications.

For the DP-800 exam, you should understand the structure of language model responses, how to extract values using SQL JSON functions, how to handle different response formats, and the best practices for securely and efficiently processing AI-generated output.

Where Response Extraction Fits in a RAG Workflow

Extracting the language model response is one of the final stages in a Retrieval-Augmented Generation (RAG) pipeline.

			
User Question
      │
      ▼
Retrieve Relevant Documents
      │
      ▼
Build Prompt
      │
      ▼
Send Request to AI Model
      │
      ▼
Receive JSON Response
      │
      ▼
Extract Generated Content
      │
      ▼
Display or Store Results

		

Without response extraction, the application cannot effectively use the AI-generated answer.

Why AI Responses Are Returned as JSON

Most AI services expose REST APIs.

REST APIs typically exchange data using JSON because it is:

Lightweight
Human-readable
Machine-readable
Widely supported
Easy to parse

Whether using Azure AI Foundry models, Azure OpenAI Service, or other AI providers, JSON is the standard response format.

Typical Language Model Response

Although the exact schema varies by provider and API version, chat completion APIs commonly return a structure similar to the following:

			
{
  "choices": [
    {
      "message": {
        "role": "assistant",
        "content": "Clustered indexes improve performance because the table rows are stored in key order."
      }
    }
  ]
}

		

The application typically extracts only the generated text, while ignoring metadata unless it is needed for monitoring or diagnostics.

Common Elements in AI Responses

A language model response may include:

Generated text
Response identifier
Model name
Completion reason
Token usage statistics
Timestamps
Metadata

Example (simplified):

			
{
  "id": "chatcmpl-123",
  "model": "gpt-4.1",
  "choices": [
    {
      "message": {
        "content": "Answer text..."
      },
      "finish_reason": "stop"
    }
  ],
  "usage": {
    "prompt_tokens": 125,
    "completion_tokens": 38,
    "total_tokens": 163
  }
}

		

Developers often extract both the generated answer and token usage for logging or cost monitoring.

Parsing JSON in SQL

SQL Server and Azure SQL Database provide built-in JSON functions.

The most commonly used are:

JSON_VALUE
JSON_QUERY
OPENJSON

These functions enable developers to retrieve values from JSON returned by an AI service.

Using JSON_VALUE

JSON_VALUE extracts a single scalar value.

Example:

			
SELECT JSON_VALUE(@Response,
'$.choices[0].message.content');

Result:

Clustered indexes improve performance because...

This is the most common method for retrieving the generated response.

Using JSON_QUERY

JSON_QUERY extracts JSON objects or arrays.

Example:

			
SELECT JSON_QUERY(@Response,
'$.choices');

This returns the complete choices array rather than a single value.

Use JSON_QUERY when you need an entire object or array for further processing.

Using OPENJSON

OPENJSON converts JSON into relational rows and columns.

Example:

			
SELECT *
FROM OPENJSON(@Response, '$.choices');

This is useful when:

Multiple completions are returned
Arrays must be processed
Nested JSON must be flattened

Extracting Token Usage

Many AI services report token consumption.

Example:

			
{
  "usage": {
      "prompt_tokens":125,
      "completion_tokens":40,
      "total_tokens":165
  }
}

		

Developers can extract these values.

Example:

			
SELECT JSON_VALUE(@Response,
'$.usage.total_tokens');

Tracking token usage helps monitor:

API costs
Performance
Resource consumption

Processing Multiple Choices

Some APIs may return multiple candidate responses.

Example:

			
{
  "choices":[
      {"message":{"content":"Option 1"}},
      {"message":{"content":"Option 2"}}
  ]
}

		

Developers can use OPENJSON to iterate through the array and select the preferred response.

Storing AI Responses

Generated responses may be:

Displayed to users
Saved to SQL tables
Logged for auditing
Indexed for future retrieval
Used by downstream workflows

Example table:

RequestID	UserQuestion	AIResponse	DateGenerated

Proper storage supports auditing, analytics, and troubleshooting.

Validating Responses

Applications should validate AI responses before using them.

Check for:

Missing content
Empty responses
Malformed JSON
Unexpected schema
API errors

Validation improves application reliability.

Handling API Errors

Not every REST call succeeds.

Possible errors include:

Authentication Failure

Examples:

Invalid token
Expired credentials

Network Errors

Examples:

Timeout
DNS failure
Connection failure

Invalid Request

Examples:

Malformed JSON
Missing prompt
Unsupported parameters

Rate Limiting

Example:

429 Too Many Requests

Applications should implement retry logic using exponential backoff where appropriate.

Finish Reasons

Many chat completion APIs include a finish reason.

Examples:

stop
length
content_filter

Meaning:

Finish Reason	Description
stop	Normal completion
length	Maximum token limit reached
content_filter	Response filtered by safety system

Applications may use this information to determine whether a response is complete.

Processing Structured Output

Some prompts request JSON output rather than plain text.

Example response:

			
{
  "summary":"Order shipped.",
  "priority":"High"
}

SQL JSON functions can extract each property individually.

Example:

			
SELECT JSON_VALUE(@Response,
'$.summary');

Structured outputs are particularly useful for workflow automation.

Security Considerations

When processing AI responses:

Validate all returned data.
Do not assume responses are always correct.
Avoid executing generated SQL without validation.
Protect sensitive information.
Log responses securely.
Apply least-privilege access controls.

Even trusted AI services should be treated as external systems whose outputs require validation.

Performance Considerations

Large responses require:

More network bandwidth
More parsing time
More storage
More tokens

Developers should:

Limit response length where appropriate.
Extract only required fields.
Avoid storing unnecessary metadata.
Archive logs according to retention policies.

Common Mistakes

Assuming Every Response Has the Same Schema

Different AI services and API versions may return different JSON structures.

Ignoring Errors

Applications should always check for API failures before attempting to parse the response.

Parsing Entire JSON Documents

Extract only the required values to improve efficiency.

Not Validating Responses

Malformed or incomplete responses should be handled gracefully.

Ignoring Token Usage

Monitoring token consumption helps control costs.

Best Practices

Parse responses using SQL JSON functions.
Use JSON_VALUE for scalar values.
Use JSON_QUERY for objects and arrays.
Use OPENJSON for arrays and complex JSON.
Validate response schemas before processing.
Log errors separately from successful responses.
Track token usage for monitoring and optimization.
Limit stored data to what is necessary.
Handle rate limits and transient failures gracefully.
Design applications to tolerate API schema changes when possible.

Real-World Example

A customer asks:

“Summarize this support ticket.”

The application:

Retrieves ticket information from SQL.
Sends it to a language model.
Receives:

			
{
  "choices":[
    {
      "message":{
        "content":"The customer reports intermittent login failures caused by expired authentication tokens."
      }
    }
  ]
}

		

The application extracts:

			
SELECT JSON_VALUE(@Response,
'$.choices[0].message.content');

The extracted summary is displayed to the support agent and optionally stored for future reference.

DP-800 Exam Tips

Remember these key points for the exam:

Most language model APIs return JSON responses.
JSON_VALUE extracts individual scalar values.
JSON_QUERY retrieves JSON objects or arrays.
OPENJSON converts JSON arrays and objects into relational data.
Applications should validate AI responses before using them.
Token usage information helps monitor API costs.
Finish reasons indicate how the model completed generation.
Handle API errors, rate limits, and malformed responses gracefully.
Store only the data needed for business purposes.
AI-generated output should always be treated as data that requires validation before use.

Practice Exam Questions

Question 1

A SQL application receives a JSON response from a language model and needs to extract the generated answer.

Which SQL function is most appropriate for retrieving a single text value?

A. JSON_VALUE

B. JSON_QUERY

C. OPENJSON

D. STRING_SPLIT

Answer: A

Explanation:
JSON_VALUE extracts a single scalar value from a JSON document, making it ideal for retrieving the generated response text.

Question 2

A developer wants to retrieve the entire choices array from a language model response.

Which SQL function should be used?

A. ROW_NUMBER

B. JSON_QUERY

C. MERGE

D. JSON_VALUE

Answer: B

Explanation:
JSON_QUERY returns JSON objects or arrays rather than individual scalar values, making it appropriate for extracting the complete choices array.

Question 3

When is OPENJSON most useful?

A. When extracting a single property value.

B. When converting JSON arrays into relational rows and columns.

C. When generating embeddings.

D. When creating vector indexes.

Answer: B

Explanation:
OPENJSON parses JSON arrays and objects into tabular data that can be queried using SQL.

Question 4

Why should applications validate AI responses before using them?

A. JSON responses are always encrypted.

B. Validation reduces database storage requirements.

C. AI responses may be malformed, incomplete, or contain unexpected structures.

D. Validation automatically reduces token usage.

Answer: C

Explanation:
Applications should verify that responses are valid, complete, and conform to the expected schema before processing them.

Question 5

A developer wants to monitor AI service costs.

Which information should be extracted from the response?

A. The database transaction log.

B. Vector dimensions.

C. Token usage statistics.

D. Query execution plans.

Answer: C

Explanation:
Many AI APIs return token usage information, which is useful for monitoring API consumption and estimating costs.

Question 6

What does a finish reason of stop typically indicate?

A. The request exceeded the maximum token limit.

B. The response was blocked by a content filter.

C. The model completed the response normally.

D. Authentication failed.

Answer: C

Explanation:
A finish reason of stop indicates that the model reached a natural completion point without interruption.

Question 7

A developer receives multiple candidate responses from an AI service.

Which SQL feature is best suited for processing all returned responses?

A. JSON_VALUE

B. OPENJSON

C. GROUP BY

D. FOR JSON AUTO

Answer: B

Explanation:
OPENJSON can iterate through arrays, making it ideal for processing multiple response choices.

Question 8

Which practice best improves the reliability of applications consuming AI responses?

A. Assume every response follows the same JSON schema.

B. Execute AI-generated SQL statements without review.

C. Validate the response structure and handle errors gracefully.

D. Ignore API error messages.

Answer: C

Explanation:
Validating responses and implementing robust error handling help applications remain reliable even when API responses change or errors occur.

Question 9

Why should developers avoid storing unnecessary metadata from AI responses?

A. Metadata prevents JSON parsing.

B. It can increase storage requirements without providing business value.

C. Metadata invalidates embeddings.

D. Metadata reduces retrieval accuracy.

Answer: B

Explanation:
Storing only the required information minimizes storage costs and simplifies downstream processing.

Question 10

A SQL application receives the following JSON:

			
{
  "choices":[
    {
      "message":{
        "content":"The shipment will arrive tomorrow."
      }
    }
  ]
}

		

Which value should typically be presented to the end user?

A. The complete JSON document.

B. The choices array.

C. The generated text contained in message.content.

D. The API response identifier.

Answer: C

Explanation:
The value stored in message.content contains the natural-language response generated by the language model and is typically the information displayed to users.

Go to the DP-800 Exam Prep Hub main page

DP-800, Microsoft Certification August 1, 2026

DP-800 Practice Exam #1 (30 questions)

This post/practice exam is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.

Question 1 (Single Answer)

You are designing a database for an online retail application. The Orders table will contain millions of records, and queries will frequently retrieve orders for a single customer ordered by purchase date.

Which index design provides the BEST performance?

A. Create a clustered index on OrderDate

B. Create a clustered index on OrderID and a nonclustered index on CustomerID

C. Create a clustered index on CustomerID, OrderDate

D. Create a nonclustered columnstore index on all columns

Answer: C

Explanation

Since most queries filter by CustomerID and sort by OrderDate, a clustered index on (CustomerID, OrderDate) physically organizes the data in the same order as the most common access pattern, minimizing page reads and sorting.

A does not optimize customer lookups.
B optimizes customer filtering but still requires additional sorting.
D is designed primarily for analytical workloads rather than OLTP.

Question 2 (Choose TWO)

Your organization wants to improve the security of an Azure SQL Database.

Which TWO features help protect sensitive information?

A. Dynamic Data Masking

B. SQL Server Agent

C. Row-Level Security

D. Query Store

E. Automatic Tuning

Choose TWO answers.

Answers:

✅ A

✅ C

Explanation

Dynamic Data Masking hides sensitive values from unauthorized users.

Row-Level Security restricts which rows users can access.

SQL Server Agent, Query Store, and Automatic Tuning are not security features.

Question 3 (Scenario)

A company is building a Retrieval-Augmented Generation (RAG) solution.

Customer manuals have already been converted into embeddings and stored in a vector index.

A user asks:

“How do I replace the printer toner?”

What should happen NEXT?

A. Generate new embeddings for every document.

B. Perform a vector similarity search using the user’s question embedding.

C. Retrain the language model.

D. Build a clustered index.

Answer: B

Explanation

After embeddings already exist, the user question is embedded and compared against the vector index to retrieve the most relevant documents before prompting the language model.

Question 4 (Fill in the Blank)

Complete the following statement.

The SQL clause most commonly used to convert relational query results into JSON documents is:

A. FOR XML

B. OPENJSON

C. JSON_VALUE

D. FOR JSON

Answer: D

Explanation

FOR JSON converts relational data into JSON.

FOR JSON AUTO automatically generates JSON.
FOR JSON PATH allows customized JSON structures.

Question 5 (Choose THREE)

A database developer wants to create high-quality prompts for a language model.

Which THREE practices are recommended?

A. Include only relevant retrieved context.

B. Include every available document.

C. Clearly specify the model’s task.

D. Remove duplicate retrieved information.

E. Leave instructions ambiguous.

Choose THREE answers.

Answers

✅ A

✅ C

✅ D

Explanation

Effective prompts:

include only relevant context,
provide clear instructions,
remove duplicate or unnecessary information.

Large, irrelevant prompts increase costs and often reduce answer quality.

Question 6 (Match the Answers)

Match each SQL JSON function with its purpose.

Function	Purpose
1. JSON_VALUE	A. Returns a JSON object or array
2. JSON_QUERY	B. Converts JSON into relational rows
3. OPENJSON	C. Returns a scalar value

Answer

Function	Correct Match
JSON_VALUE	C
JSON_QUERY	A
OPENJSON	B

Explanation

JSON_VALUE returns a scalar value.
JSON_QUERY returns objects or arrays.
OPENJSON converts JSON into tabular data.

Question 7 (Single Answer)

Which similarity metric is generally recommended when comparing normalized embedding vectors?

A. Manhattan Distance

B. Euclidean Distance

C. Hamming Distance

D. Cosine Similarity

Answer: D

Explanation

Cosine similarity measures the angle between vectors and is the most commonly used similarity metric for normalized embeddings because it focuses on semantic direction rather than vector magnitude.

Question 8 (Scenario)

Your company stores customer support articles inside Azure SQL Database.

The support team wants an AI assistant that always answers questions using the latest documentation stored in the database.

Which solution should you recommend?

A. Fine-tune the language model every night.

B. Use Retrieval-Augmented Generation (RAG).

C. Train a custom transformer model.

D. Store every support article inside the prompt.

Answer: B

Explanation

RAG retrieves current documentation at query time, ensuring responses reflect the latest information without retraining the language model.

Question 9 (Ordering)

A developer is building a SQL-based RAG application using sp_invoke_external_rest_endpoint.

Arrange the following steps in the correct order.

Retrieve relevant documents.
Call the language model.
Generate embeddings for the user question.
Construct the prompt.

Correct Order

3 → 1 → 4 → 2

Explanation

The workflow is:

Generate an embedding for the user’s question.
Retrieve similar documents.
Build the prompt using the retrieved context.
Send the prompt to the language model.

Question 10 (Scenario-Based)

A company has implemented hybrid search that combines keyword search and vector search.

The search results are merged using Reciprocal Rank Fusion (RRF).

What is the primary purpose of RRF?

A. Generate embeddings.

B. Merge and re-rank results from multiple retrieval methods.

C. Compress vector indexes.

D. Convert SQL data into JSON.

Answer: B

Explanation

Reciprocal Rank Fusion (RRF) combines ranked result lists from different retrieval methods (such as keyword search and vector search) into a single ranking. This often improves search quality by leveraging the strengths of each retrieval technique.

Question 11 (Single Answer)

Your company maintains several stored procedures that perform complex business logic. The procedures are executed thousands of times each hour, but the execution plans frequently become inefficient because parameter values vary significantly.

Which feature should you implement to reduce parameter sensitivity issues?

A. Enable Query Store

B. Use Parameter Sensitive Plan (PSP) optimization

C. Create a clustered columnstore index

D. Enable Dynamic Data Masking

Answer: B

Explanation

Parameter Sensitive Plan (PSP) optimization allows SQL Server to maintain multiple execution plans for different parameter value ranges, improving performance when parameter distributions vary significantly.

Query Store helps monitor plans but does not solve parameter sensitivity by itself.
Columnstore indexes target analytical workloads.
Dynamic Data Masking is unrelated to performance.

Question 12 (Choose TWO)

You are developing a SQL application that calls an Azure AI model by using sp_invoke_external_rest_endpoint.

Which two components are typically required in the REST request?

A. HTTP headers

B. JSON payload

C. XML schema

D. SQL CLR assembly

E. SQL Agent Job

Choose TWO answers.

Answers

✅ A

✅ B

Explanation

REST requests to AI services generally require:

HTTP headers (authentication, content type)
A JSON request body containing the prompt and parameters

The remaining options are unrelated.

Question 13 (Scenario)

A financial institution is implementing Row-Level Security (RLS).

Managers should see records only for employees in their own department.

Which component enforces this behavior?

A. Dynamic Data Masking

B. Security policy using a predicate function

C. Transparent Data Encryption

D. Query Store

Answer: B

Explanation

Row-Level Security uses an inline table-valued predicate function combined with a security policy to filter rows automatically based on the executing user’s context.

Question 14 (Match the Answers)

Match each SQL object with its primary purpose.

SQL Object	Purpose
1. View	A. Stores executable business logic
2. Stored Procedure	B. Represents a virtual table
3. Trigger	C. Executes automatically after data modifications

Answer

SQL Object	Correct Match
View	B
Stored Procedure	A
Trigger	C

Explanation

Views provide virtual tables.
Stored procedures encapsulate reusable logic.
Triggers automatically execute when INSERT, UPDATE, or DELETE events occur.

Question 15 (Single Answer)

A developer needs to generate embeddings for thousands of product descriptions before building a vector index.

What should happen first?

A. Create the vector index.

B. Build the hybrid search pipeline.

C. Generate embeddings for each document.

D. Call the language model.

Answer: C

Explanation

Embeddings must exist before a vector index can be populated. The typical workflow is:

Generate embeddings.
Store vectors.
Create/populate the vector index.
Perform similarity search.

Question 16 (Choose THREE)

Which three practices improve database security?

A. Enable Transparent Data Encryption (TDE)

B. Implement least-privilege permissions

C. Disable authentication logging

D. Apply Dynamic Data Masking where appropriate

E. Grant db_owner to all developers

Choose THREE answers.

Answers

✅ A

✅ B

✅ D

Explanation

These practices strengthen database security by protecting data at rest, limiting user permissions, and masking sensitive information.

Granting excessive permissions and disabling auditing reduce security.

Question 17 (Scenario)

Your organization uses Azure SQL Database.

Developers frequently overwrite one another’s schema changes during deployment.

Management wants schema changes tracked, versioned, reviewed, and automatically deployed.

Which technology best satisfies these requirements?

A. Query Store

B. SQL Database Projects with Git and CI/CD

C. SQL Profiler

D. SQL Server Agent

Answer: B

Explanation

SQL Database Projects integrate with source control systems and CI/CD pipelines, enabling controlled schema versioning, peer review, automated validation, and repeatable deployments.

Question 18 (Fill in the Blank)

Complete the statement.

The SQL function most commonly used to retrieve a single scalar value from a JSON document is:

A. OPENJSON

B. JSON_QUERY

C. JSON_VALUE

D. FOR JSON PATH

Answer: C

Explanation

JSON_VALUE extracts individual scalar values such as strings, numbers, or Boolean values from JSON documents.

Question 19 (Scenario-Based)

A retail company has implemented hybrid search using both keyword search and vector search.

Testing shows that keyword search finds exact product numbers, while vector search finds semantically similar products.

Management wants both result sets combined into one ranked list.

Which technique should be used?

A. Euclidean Distance

B. Principal Component Analysis

C. Reciprocal Rank Fusion (RRF)

D. K-Means Clustering

Answer: C

Explanation

Reciprocal Rank Fusion combines ranked results from multiple retrieval methods, producing a single ranking that benefits from both lexical and semantic matching.

Question 20 (Multi-Answer)

A SQL developer is preparing structured customer information before sending it to a language model.

Which three practices are recommended?

A. Remove sensitive information that is not required.

B. Convert relational results into JSON.

C. Include every available database column.

D. Send only the fields needed for the prompt.

E. Ignore row-level security because the AI model is trusted.

Choose THREE answers.

Answers

✅ A

✅ B

✅ D

Explanation

Preparing structured data for AI involves:

Removing unnecessary or sensitive information.
Converting relational data to JSON.
Sending only relevant fields to minimize token usage and improve performance.

Including all columns wastes tokens and may expose confidential information. Existing security controls should remain in effect.

Question 21 (Scenario-Based)

A company is building a customer support chatbot using Retrieval-Augmented Generation (RAG). Product manuals are updated daily, and management wants the chatbot to use the newest documentation immediately without retraining the language model.

Which architecture best satisfies this requirement?

A. Fine-tune the language model every evening.

B. Store all manuals directly in the prompt.

C. Use a vector index to retrieve relevant documents during each user query.

D. Convert all manuals into stored procedures.

Answer: C

Explanation

RAG retrieves the most relevant documents at query time using a vector search, allowing the chatbot to use newly added documentation without retraining the model.

Fine-tuning is expensive and unnecessary for frequently changing data.
Including all manuals in every prompt exceeds token limits.
Stored procedures cannot replace document retrieval.

Question 22 (Choose TWO)

Which TWO characteristics are true of embedding vectors?

A. Similar meanings produce vectors that are close together.

B. Embeddings store the original document text.

C. Embeddings represent semantic meaning numerically.

D. Embeddings require clustered indexes.

E. Embeddings replace relational databases.

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

Embeddings are numerical representations of semantic meaning. Similar concepts generate vectors that are close together within vector space.

Question 23 (Single Answer)

A developer wants to improve the performance of a vector similarity search.

Which action provides the greatest benefit?

A. Increase the SQL transaction log size.

B. Create an appropriate vector index.

C. Enable Dynamic Data Masking.

D. Compress the database backup.

Answer: B

Explanation

Vector indexes dramatically improve similarity search performance by reducing the number of vectors that must be examined during nearest-neighbor searches.

Question 24 (Scenario-Based)

A SQL application calls an Azure AI model by using sp_invoke_external_rest_endpoint.

The returned JSON contains the following:

			
{
  "choices": [
    {
      "message": {
        "content": "Always validate user input."
      }
    }
  ]
}

		

Which SQL function should be used to extract only the generated response?

A. OPENJSON

B. JSON_QUERY

C. FOR JSON PATH

D. JSON_VALUE

Answer: D

Explanation

JSON_VALUE() extracts a single scalar value, making it ideal for retrieving choices[0].message.content.

Question 25 (Ordering)

Arrange the following steps for implementing vector search.

Generate embeddings.
Store embeddings in the database.
Create the vector index.
Execute similarity searches.

Correct Order

1 → 2 → 3 → 4

Explanation

Embeddings must first be generated and stored before the vector index can be created and used for similarity searches.

Question 26 (Match the Answers)

Match each AI concept with its description.

Concept	Description
1. Embedding	A. Combines keyword and vector search rankings
2. Hybrid Search	B. Numerical representation of semantic meaning
3. Reciprocal Rank Fusion	C. Executes keyword and vector searches together

Answer

Concept	Correct Match
Embedding	B
Hybrid Search	C
Reciprocal Rank Fusion	A

Explanation

Embeddings convert data into semantic vectors.
Hybrid search combines lexical and semantic retrieval.
RRF merges multiple ranked result lists into a single ranking.

Question 27 (Choose THREE)

Which THREE practices improve prompt quality for Retrieval-Augmented Generation?

A. Include only relevant retrieved documents.

B. Clearly describe the task.

C. Add duplicate context whenever possible.

D. Specify the desired output format.

E. Include unrelated reference material.

Choose THREE answers.

Answers

✅ A

✅ B

✅ D

Explanation

Good prompts:

include only relevant context,
clearly define the task,
specify the expected response format.

Duplicate or unrelated information wastes tokens and may reduce answer quality.

Question 28 (Scenario-Based)

A company stores HR information in Azure SQL Database.

Only Human Resources employees should view employee salaries, even when an AI application queries the database.

Which solution provides the BEST protection?

A. Transparent Data Encryption

B. Row-Level Security

C. Automatic Indexing

D. Query Store

Answer: B

Explanation

Row-Level Security ensures only authorized users can access rows containing sensitive salary information, regardless of whether the data is accessed directly or through an AI-enabled application.

Question 29 (Single Answer)

A developer needs to reduce API costs when sending requests to a language model.

Which action is MOST effective?

A. Increase the embedding dimensions.

B. Send every available database column.

C. Include only relevant context in the prompt.

D. Increase the maximum response tokens.

Answer: C

Explanation

Reducing unnecessary prompt content decreases token usage, lowers costs, improves latency, and often improves answer quality.

Question 30 (Comprehensive Scenario)

A software company is developing an AI-powered knowledge assistant using Azure SQL Database.

The application requirements are:

Store technical documents.
Support semantic search.
Combine keyword and vector search.
Retrieve the best documents.
Send the retrieved context to a language model.
Display AI-generated answers.
Use current documentation without retraining.

Which architecture BEST satisfies these requirements?

A. Fine-tune the language model after every documentation update.

B. Store every document inside a single SQL stored procedure.

C. Export all documents into CSV files before every query.

D. Implement a Retrieval-Augmented Generation (RAG) solution using embeddings, vector search, hybrid search, and prompt construction.

Answer: D

Explanation

A RAG architecture provides exactly the required functionality:

Documents remain in the database.
Embeddings enable semantic retrieval.
Hybrid search combines keyword and vector search.
Retrieved documents become prompt context.
The language model generates grounded responses.
Documentation updates are immediately available without retraining.

Go to the DP-800 Exam Prep Hub main page

DP-800, Microsoft Certification August 1, 2026August 1, 2026

DP-800 Practice Exam #2 (30 questions)

This post/practice exam is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.

Question 1 (Scenario-Based)

A software company is designing a SQL Server database for an online reservation system. Each reservation has a unique ReservationID that is never updated. Most transactions retrieve reservations by ReservationID.

Which indexing strategy should you recommend?

A. Create a clustered index on ReservationID.

B. Create a nonclustered index on every column.

C. Create a clustered columnstore index.

D. Do not create indexes until performance problems occur.

Answer: A

Explanation

A clustered index on a stable, unique key such as ReservationID is ideal for OLTP workloads because it provides efficient point lookups and organizes the table by the primary access path.

Nonclustered indexes on every column increase maintenance overhead.
Clustered columnstore indexes are intended for analytics.
Waiting to create indexes is not a best practice for well-understood workloads.

Question 2 (Choose TWO)

A development team wants to improve database deployment quality using SQL Database Projects.

Which TWO benefits does this approach provide?

A. Source control integration

B. Automatic vector embedding generation

C. Schema validation before deployment

D. Automatic Row-Level Security configuration

E. Automatic model fine-tuning

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

SQL Database Projects support:

Version control integration (Git, Azure DevOps, GitHub)
Build-time validation of schema changes
Automated CI/CD deployment

They do not automatically configure security policies or AI models.

Question 3 (Single Answer)

A database contains personally identifiable information (PII). Customer service representatives should see only partially masked Social Security numbers, while administrators should see the complete values.

Which feature should you implement?

A. Transparent Data Encryption

B. Row-Level Security

C. Dynamic Data Masking

D. Always Encrypted

Answer: C

Explanation

Dynamic Data Masking hides portions of sensitive data for non-privileged users while allowing authorized users to view the original values.

TDE protects data at rest.
RLS filters rows.
Always Encrypted protects data from the database engine itself and is a stronger encryption solution, but it does not provide role-based masking behavior.

Question 4 (Fill in the Blank)

Complete the statement.

The SQL function used to convert JSON arrays into relational rows is:

A. JSON_VALUE

B. JSON_QUERY

C. OPENJSON

D. FOR JSON AUTO

Answer: C

Explanation

OPENJSON parses JSON objects and arrays into relational rows and columns that can be queried using T-SQL.

Question 5 (Scenario-Based)

Your organization has implemented Retrieval-Augmented Generation (RAG).

Users report that the chatbot often provides outdated information, even though newer documentation has already been uploaded.

Which action is MOST likely to resolve the issue?

A. Increase the model temperature.

B. Regenerate embeddings for the newly added documents and update the vector index.

C. Enable Query Store.

D. Increase MAXDOP.

Answer: B

Explanation

New documents must be embedded and indexed before they can be retrieved through vector search. Without updated embeddings, the retrieval process cannot find the new content.

Question 6 (Match the Answers)

Match each database security feature with its primary purpose.

Feature	Purpose
1. Transparent Data Encryption	A. Restricts visible rows
2. Row-Level Security	B. Encrypts database files at rest
3. Dynamic Data Masking	C. Masks sensitive column values

Answer

Feature	Correct Match
Transparent Data Encryption	B
Row-Level Security	A
Dynamic Data Masking	C

Explanation

TDE encrypts the database on disk.
RLS filters rows returned to users.
DDM masks column values for unauthorized users.

Question 7 (Choose THREE)

Which THREE factors improve the quality of vector search results?

A. Generate high-quality embeddings using an appropriate embedding model.

B. Chunk large documents into meaningful sections.

C. Store embeddings as VARCHAR values.

D. Use an appropriate vector similarity metric.

E. Disable vector indexing.

Choose THREE answers.

Answers

✅ A

✅ B

✅ D

Explanation

Effective vector search depends on:

High-quality embeddings
Appropriate document chunking
Correct similarity metrics (such as cosine similarity)

Storing embeddings as text or disabling indexes reduces performance and effectiveness.

Question 8 (Scenario-Based)

A SQL application sends prompts to an Azure AI model using sp_invoke_external_rest_endpoint.

The application receives HTTP status code 429.

What does this status code indicate?

A. Authentication failed.

B. The request contains malformed JSON.

C. The service is rate limiting requests.

D. The model generated an invalid response.

Answer: C

Explanation

HTTP 429 means Too Many Requests. The application should implement retry logic with exponential backoff to handle temporary throttling.

Question 9 (Ordering)

Arrange the following CI/CD workflow in the correct order.

Commit schema changes.
Validate the SQL Database Project.
Deploy to the production environment.
Build the deployment artifact.

Correct Order

1 → 2 → 4 → 3

Explanation

The typical workflow is:

Commit changes to source control.
Validate the project during the build.
Generate the deployment artifact (such as a DACPAC).
Deploy to production through the release pipeline.

Question 10 (Comprehensive Scenario)

A company is building an AI-powered search application.

Requirements:

Support traditional keyword searches.
Support semantic similarity searches.
Combine both result sets into one ranked list.
Improve relevance without retraining the language model.

Which solution best satisfies these requirements?

A. Increase the embedding dimensions.

B. Implement hybrid search with Reciprocal Rank Fusion (RRF).

C. Fine-tune the language model every week.

D. Replace vector search with LIKE queries.

Answer: B

Explanation

Hybrid search combines keyword and vector search results. Reciprocal Rank Fusion (RRF) merges and reranks the results, improving retrieval quality by leveraging both lexical and semantic matching.

Question 11 (Scenario-Based)

A database developer creates the following query:

			
SELECT *
FROM Sales
WHERE CustomerID = 1050;

The query runs frequently against a table containing 500 million rows.

The query execution plan shows that SQL Server performs a table scan.

What should you do to improve performance?

A. Create a nonclustered index on CustomerID.

B. Enable Transparent Data Encryption.

C. Increase the database compatibility level.

D. Convert the table to JSON format.

Answer: A

Explanation

A nonclustered index on CustomerID allows SQL Server to quickly locate matching rows instead of scanning the entire table.

TDE does not improve query performance.
Compatibility changes may enable features but do not directly solve this issue.
JSON conversion would negatively impact relational query performance.

Question 12 (Choose TWO)

A developer is creating a stored procedure that will be called by an application.

Which TWO practices improve the security and reliability of the stored procedure?

A. Use parameterized inputs.

B. Grant users direct access to all underlying tables.

C. Validate input parameters.

D. Construct SQL statements using string concatenation.

E. Disable error handling.

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

Parameterized inputs reduce SQL injection risk, while input validation ensures the procedure receives expected values.

Avoid:

Direct table access when unnecessary.
Dynamic SQL built through string concatenation.
Removing error handling.

Question 13 (Single Answer)

A company wants to track query performance regressions after deploying database changes.

Which SQL feature should be used?

A. Query Store

B. Dynamic Data Masking

C. Database Mail

D. Change Tracking

Answer: A

Explanation

Query Store captures query execution information, including:

Query text
Execution plans
Runtime statistics
Historical performance

This allows administrators to identify regressions after deployments.

Question 14 (Scenario-Based)

A company stores product descriptions in Azure SQL Database.

They want customers to search for:

“comfortable shoes for hiking”

and retrieve products described as:

“lightweight trail footwear designed for long walks.”

A traditional keyword search does not return the correct results.

What should you implement?

A. Foreign key constraints

B. Vector embeddings and similarity search

C. Additional clustered indexes

D. Data compression

Answer: B

Explanation

Keyword search depends on exact terms. Vector search uses embeddings to understand semantic similarity, allowing conceptually related results to be returned.

Question 15 (Fill in the Blank)

Complete the statement.

The process of converting text, images, or other data into numerical representations that capture semantic meaning is called:

A. Tokenization

B. Index fragmentation

C. Embedding

D. Encryption

Answer: C

Explanation

An embedding converts information into a numerical vector representation that can be compared mathematically for similarity.

Question 16 (Match the Answers)

Match each SQL performance feature with its purpose.

Feature	Purpose
1. Query Store	A. Automatically adjusts database performance settings
2. Automatic Tuning	B. Stores historical query performance information
3. Columnstore Index	C. Optimizes analytical queries over large datasets

Answer

Feature	Correct Match
Query Store	B
Automatic Tuning	A
Columnstore Index	C

Explanation

Query Store tracks query history.
Automatic Tuning can recommend or apply performance improvements.
Columnstore indexes accelerate analytical workloads.

Question 17 (Choose THREE)

You are implementing a Retrieval-Augmented Generation solution.

Which THREE components are required?

A. A data source containing grounding information

B. An embedding model

C. A retrieval mechanism

D. A clustered index on every table

E. A database trigger for every document

Choose THREE answers.

Answers

✅ A

✅ B

✅ C

Explanation

A RAG system requires:

Source information.
Embeddings to represent semantic meaning.
Retrieval to find relevant context.

Clustered indexes and triggers are not required components of RAG.

Question 18 (Scenario-Based)

A developer sends database information to a language model.

The prompt contains:

Customer name
Customer address
Internal account identifier
Product question

The model only needs the product question and relevant product information.

What should the developer do?

A. Include all data because larger prompts improve accuracy.

B. Remove unnecessary customer information before creating the prompt.

C. Disable database security features.

D. Increase the model temperature.

Answer: B

Explanation

Only relevant information should be sent to the language model.

Benefits include:

Reduced token usage
Lower cost
Better privacy
Improved response quality

Question 19 (Single Answer)

A developer needs to retrieve an entire JSON object from an AI model response.

Which SQL function should be used?

A. JSON_VALUE

B. JSON_QUERY

C. LEN

D. STRING_AGG

Answer: B

Explanation

JSON_QUERY returns JSON objects or arrays.

Example:

SELECT JSON_QUERY(@response, '$.choices');

JSON_VALUE is used only for scalar values.

Question 20 (Comprehensive Scenario)

A company is deploying a SQL Database Project through Azure DevOps.

The deployment process must:

Validate schema changes
Prevent unauthorized database modifications
Automatically deploy approved changes

Which approach should be implemented?

A. Allow developers to manually modify production databases.

B. Use source control, build validation, and automated deployment pipelines.

C. Store database scripts on local developer machines.

D. Disable all database permissions during deployment.

Answer: B

Explanation

A proper CI/CD workflow includes:

Source control management
Automated builds
Validation
Deployment approvals
Automated release pipelines

This improves consistency, security, and reliability.

Question 21 (Scenario-Based)

A company has implemented a vector search solution in Azure SQL Database.

Users report that searches sometimes return documents that contain similar words but do not answer the actual question.

The development team wants results that consider both exact keyword matches and semantic similarity.

What should the team implement?

A. Increase the number of database indexes.

B. Hybrid search combining keyword and vector search.

C. Replace embeddings with relational columns.

D. Increase SQL Server memory allocation.

Answer: B

Explanation

Hybrid search combines:

Traditional lexical search (keyword matching)
Vector search (semantic similarity)

This improves retrieval quality because each method addresses different search scenarios.

Question 22 (Choose TWO)

A developer is evaluating vector search performance.

Which TWO factors should be considered when selecting a similarity metric?

A. The type of embedding model being used.

B. The database recovery model.

C. Whether vectors are normalized.

D. The number of database users.

E. The table’s foreign keys.

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

Similarity metrics should align with:

The characteristics of the embedding model.
Whether vectors are normalized.

Common metrics include:

Cosine similarity
Euclidean distance
Dot product

Database recovery models and relational constraints do not affect vector similarity calculations.

Question 23 (Single Answer)

A developer wants to combine rankings from a vector search query and a keyword search query.

Which algorithm should be used?

A. Reciprocal Rank Fusion (RRF)

B. K-means clustering

C. Gradient descent

D. Binary search

Answer: A

Explanation

Reciprocal Rank Fusion combines multiple ranked result lists into a single ranking.

It is commonly used in hybrid search solutions because it does not require the scores from different search systems to be directly comparable.

Question 24 (Scenario-Based)

A company uses an AI assistant to answer questions about internal policies.

The assistant sometimes generates responses that are not supported by company documentation.

Which RAG improvement should be implemented?

A. Remove document retrieval from the workflow.

B. Increase the model temperature.

C. Provide retrieved documents as grounding context in the prompt.

D. Train users to write longer questions.

Answer: C

Explanation

RAG reduces hallucinations by providing the language model with relevant retrieved information as context.

The prompt should include:

User question
Retrieved documents
Instructions to answer using provided information

Question 25 (Ordering)

Arrange the following steps for processing a user question in a RAG application.

Send the augmented prompt to the language model.
Generate an embedding for the user question.
Retrieve similar documents.
Combine the question and retrieved context.

Correct Order

2 → 3 → 4 → 1

Explanation

A RAG workflow follows these steps:

Convert the question into an embedding.
Search the vector index.
Build the augmented prompt.
Send it to the language model.

Question 26 (Match the Answers)

Match each technology with its purpose.

Technology	Purpose
1. Vector Index	A. Generates natural language responses
2. Embedding Model	B. Enables efficient similarity searches
3. Language Model	C. Converts content into numerical vectors

Answer

Technology	Correct Match
Vector Index	B
Embedding Model	C
Language Model	A

Explanation

Vector indexes optimize searching through embeddings.
Embedding models convert data into numerical representations.
Language models generate responses.

Question 27 (Choose THREE)

A developer is implementing an enterprise AI assistant using SQL data.

Which THREE security practices should be followed?

A. Apply least-privilege permissions.

B. Remove unnecessary sensitive information from prompts.

C. Log prompts and responses securely.

D. Grant all AI services administrator permissions.

E. Disable auditing.

Choose THREE answers.

Answers

✅ A

✅ B

✅ C

Explanation

Secure AI solutions should:

Follow least privilege.
Minimize sensitive data exposure.
Maintain secure logging and monitoring.

Granting excessive permissions and disabling auditing increase security risks.

Question 28 (Scenario-Based)

A developer receives this response from an AI service:

			
{
 "choices": [
   {
     "message": {
       "content": "The warranty expires after two years."
     }
   }
 ]
}

		

The developer needs to store only the generated answer in a SQL table.

Which query should be used?

SELECT JSON_VALUE(@response,'$.choices');

SELECT JSON_QUERY(@response,'$.choices[0]');

SELECT JSON_VALUE(@response,'$.choices[0].message.content');

SELECT OPENJSON(@response);

Answer: C

Explanation

JSON_VALUE extracts scalar values.

The generated answer is stored at:

$.choices[0].message.content

Question 29 (Single Answer)

A company notices that a SQL query became slower after a database deployment.

Which feature should administrators use to compare previous and current query performance?

A. Query Store

B. Data Masking

C. Database Mail

D. SQL Server Agent

Answer: A

Explanation

Query Store maintains historical information about:

Queries
Execution plans
Runtime statistics

It helps identify performance regressions after changes.

Question 30 (Comprehensive Scenario)

A company is creating an AI-powered document assistant.

Requirements:

Documents are stored in Azure SQL Database.
Users search using natural language.
Search must return relevant documents even when exact words differ.
Exact terms such as product IDs must still work.
Responses must be generated using retrieved information.
New documents should become available without model retraining.

Which architecture should be implemented?

A. Keyword search only with SQL LIKE queries.

B. Fine-tune the language model whenever documents change.

C. Implement RAG using embeddings, vector search, hybrid search, and prompt augmentation.

D. Store all documents directly in the language model.

Answer: C

Explanation

The correct architecture is a Retrieval-Augmented Generation solution:

Generate embeddings for documents.
Store embeddings in a vector-enabled database.
Perform hybrid search:
- Keyword search for exact matches.
- Vector search for semantic similarity.
Add retrieved context to the prompt.
Generate a grounded response.

This allows new documents to become available without retraining the model.

Go to the DP-800 Exam Prep Hub main page

DP-800, Microsoft Certification, Uncategorized August 1, 2026

DP-800 Practice Exam #4 (30 questions)

This post/practice exam is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.

Question 1 (Scenario-Based)

A financial services company is designing a transaction database.

The database must:

Process thousands of transactions per second.
Support frequent inserts and updates.
Retrieve transactions by TransactionID.
Maintain strong transactional consistency.

Which table design should you recommend?

A. Create a clustered index on TransactionID.

B. Create a clustered columnstore index on the transaction table.

C. Store transactions as JSON documents.

D. Create indexes only after performance issues occur.

Answer: A

Explanation

A clustered index on a transaction key is appropriate for an OLTP workload.

Benefits:

Fast lookups by TransactionID.
Efficient storage organization.
Good support for inserts and updates.

Why other answers are incorrect:

B: Columnstore indexes are optimized for analytics and large scans.
C: JSON storage is not ideal for transactional relational workloads.
D: Index design should be planned during database development.

Question 2 (Choose TWO)

A developer is creating a SQL stored procedure used by multiple applications.

Which TWO practices should be implemented?

A. Use parameters instead of concatenating user input.

B. Grant applications direct access to every database table.

C. Implement TRY…CATCH error handling.

D. Disable transaction handling.

E. Store database credentials inside the procedure.

Choose TWO answers.

Answers:

✅ A
✅ C

Explanation

Stored procedures should:

Use parameters to prevent SQL injection.
Include error handling to manage failures.

Incorrect approaches:

Direct table access violates security principles.
Credentials should never be stored in code.
Transactions should be managed appropriately.

Question 3 (Single Answer)

A company needs to prevent users from seeing specific rows in a table based on their department.

Which feature should be implemented?

A. Dynamic Data Masking

B. Row-Level Security

C. Transparent Data Encryption

D. Always Encrypted

Answer: B

Explanation

Row-Level Security (RLS) controls which rows users can access.

Example:

A sales employee can view only customers assigned to their region.

Comparison:

Feature	Purpose
Dynamic Data Masking	Hides column values
RLS	Filters rows
TDE	Encrypts data at rest
Always Encrypted	Protects sensitive data from database administrators

Question 4 (Fill in the Blank)

Complete the statement.

The SQL feature that allows developers to analyze historical query execution plans and runtime statistics is:

A. Query Store

B. SQL Server Agent

C. Database Mail

D. Resource Governor

Answer: A

Explanation

Query Store captures:

Query text
Execution plans
Runtime statistics
Performance history

It is commonly used to troubleshoot regressions after deployments.

Question 5 (Scenario-Based)

An e-commerce company has a Products table.

The following query is executed frequently:

			
SELECT ProductName, Price
FROM Products
WHERE CategoryID = 25;

The table contains 200 million rows.

The query currently performs a full table scan.

What should you implement?

A. A nonclustered index on CategoryID.

B. Transparent Data Encryption.

C. Convert the table into XML.

D. Increase the database recovery model.

Answer: A

Explanation

A nonclustered index allows SQL Server to locate products by CategoryID without scanning the entire table.

The other options do not improve query lookup performance.

Question 6 (Matching)

Match each feature with its purpose.

Feature	Purpose
1. Query Store	A. Encrypts stored database files
2. Transparent Data Encryption	B. Tracks query performance history
3. Dynamic Data Masking	C. Hides sensitive column values

Answer

Feature	Match
Query Store	B
Transparent Data Encryption	A
Dynamic Data Masking	C

Explanation

Query Store helps analyze query performance.
TDE encrypts data files.
DDM masks sensitive values.

Question 7 (Scenario-Based)

A development team manages SQL schemas using SQL Database Projects.

The team requires:

Database changes stored in Git.
Automated validation before deployment.
Repeatable deployments across environments.

Which approach should be used?

A. Manually execute scripts in production.

B. Use SQL Database Projects with a CI/CD pipeline.

C. Allow developers to change production directly.

D. Store scripts only on local computers.

Answer: B

Explanation

SQL Database Projects support modern database DevOps practices:

Source control integration.
Automated builds.
Schema validation.
Deployment automation.

Question 8 (Choose THREE)

A company is building a semantic search application.

Which THREE components are required?

A. An embedding model

B. Vector storage

C. Similarity search

D. Database Mail

E. SQL Server Agent jobs

Choose THREE answers.

Answers:

✅ A
✅ B
✅ C

Explanation

Semantic search requires:

Converting content into embeddings.
Storing those vectors.
Searching vectors based on similarity.

Database Mail and SQL Server Agent are unrelated.

Question 9 (Scenario-Based)

A company has implemented Retrieval-Augmented Generation (RAG).

The application retrieves documents but sometimes produces incorrect answers.

The retrieved documents are accurate.

What should the developer review first?

A. The prompt instructions sent to the language model.

B. The database backup schedule.

C. The database file size.

D. The transaction isolation level.

Answer: A

Explanation

When retrieval is correct but answers are incorrect, the prompt design should be reviewed.

The prompt should:

Clearly define the task.
Include retrieved context.
Instruct the model to use provided information.

Question 10 (Single Answer)

A developer receives this JSON response from an AI service:

			
{
  "answer": "The policy expires after one year."
}

Which SQL function should extract the answer value?

A. JSON_QUERY

B. OPENJSON

C. JSON_VALUE

D. FOR JSON PATH

Answer: C

Explanation

JSON_VALUE extracts scalar values from JSON.

Example:

SELECT JSON_VALUE(@response,'$.answer');

Other functions:

JSON_QUERY returns objects or arrays.
OPENJSON converts JSON into rows.
FOR JSON PATH creates JSON output.

Question 11 (Scenario-Based)

A company has an Azure SQL Database containing customer activity data.

The following query is executed frequently:

			
SELECT 
    CustomerID,
    LastLoginDate,
    AccountStatus
FROM Customers
WHERE CustomerID = @CustomerID;

		

The query execution plan shows an expensive key lookup operation.

What should you do to improve performance?

A. Create a covering nonclustered index that includes LastLoginDate and AccountStatus.

B. Remove all indexes from the table.

C. Convert the table to a clustered columnstore table.

D. Enable Transparent Data Encryption.

Answer: A

Explanation

A covering index contains all columns needed by the query.

Example:

			
CREATE INDEX IX_Customers_CustomerID
ON Customers(CustomerID)
INCLUDE (LastLoginDate, AccountStatus);

This allows SQL Server to retrieve all required data directly from the index without performing additional lookups.

Incorrect answers:

B: Removing indexes reduces performance.
C: Columnstore indexes are designed for analytics.
D: Encryption does not improve query performance.

Question 12 (Choose TWO)

A developer wants to improve the reliability of database application code.

Which TWO practices should be implemented?

A. Use explicit transactions when multiple operations must succeed together.

B. Ignore transaction failures because SQL Server automatically retries everything.

C. Use appropriate error handling.

D. Store business logic only in client applications.

E. Remove constraints to improve performance.

Choose TWO answers.

Answers:

✅ A
✅ C

Explanation

Reliable database applications use:

Transactions for atomic operations.
Error handling to properly manage failures.

Incorrect:

SQL Server does not automatically handle all failures.
Constraints protect data integrity.
Business logic can exist in stored procedures when appropriate.

Question 13 (Single Answer)

A company needs to ensure that database administrators cannot view sensitive customer information.

Which feature provides the strongest protection?

A. Dynamic Data Masking

B. Always Encrypted

C. Row-Level Security

D. Transparent Data Encryption

Answer: B

Explanation

Always Encrypted protects sensitive data from being viewed by the database engine or administrators.

Comparison:

Feature	Protection
Dynamic Data Masking	Hides displayed values
RLS	Restricts rows
TDE	Encrypts database files
Always Encrypted	Protects data from unauthorized database access

Question 14 (Scenario-Based)

A developer notices a query performs poorly.

The execution plan shows:

A table scan
A missing index recommendation
High logical reads

What should the developer do first?

A. Review the query execution plan and evaluate appropriate indexing.

B. Increase the language model temperature.

C. Enable database encryption.

D. Convert relational tables into JSON.

Answer: A

Explanation

Execution plans identify:

Expensive operators.
Missing indexes.
Inefficient query patterns.

The correct optimization process is to analyze the workload before making changes.

Question 15 (Fill in the Blank)

Complete the statement.

The process of combining keyword search results with vector search results is called:

A. Data normalization

B. Hybrid search

C. Data partitioning

D. Tokenization

Answer: B

Explanation

Hybrid search combines:

Lexical search (keywords)
Semantic search (vectors)

This improves retrieval accuracy because both exact matching and meaning are considered.

Question 16 (Matching)

Match each AI search concept with its purpose.

Concept	Purpose
1. Embedding	A. Combines multiple ranked searches
2. Reciprocal Rank Fusion	B. Converts content into vectors
3. Vector Search	C. Finds similar content based on vectors

Answer

Concept	Match
Embedding	B
Reciprocal Rank Fusion	A
Vector Search	C

Explanation

Embeddings represent content numerically.
Vector search finds semantically similar information.
RRF combines ranked lists from multiple retrieval methods.

Question 17 (Choose THREE)

A company is securing an AI-enabled database application.

Which THREE actions should be implemented?

A. Use managed identities for Azure resources.

B. Apply least-privilege access.

C. Remove unnecessary sensitive data from prompts.

D. Share database administrator credentials with developers.

E. Disable auditing.

Choose THREE answers.

Answers:

✅ A
✅ B
✅ C

Explanation

Secure AI solutions should:

Minimize credential exposure.
Restrict permissions.
Reduce sensitive information sent to AI services.

Incorrect:

Shared administrator credentials violate security principles.
Auditing supports governance and compliance.

Question 18 (Scenario-Based)

A developer creates a RAG solution.

The retrieval process returns:

Product manuals
Customer reviews
Internal notes

The language model frequently references customer reviews instead of official manuals.

What should the developer implement?

A. Increase the number of unrelated documents retrieved.

B. Improve retrieval filtering and document ranking.

C. Remove all vector embeddings.

D. Disable prompt instructions.

Answer: B

Explanation

RAG quality depends on retrieval quality.

Possible improvements:

Metadata filtering.
Better ranking.
Improved chunking.
Adjusted retrieval parameters.

The model can only use the information it receives.

Question 19 (Scenario-Based)

A developer calls an Azure AI service from SQL Server using:

sp_invoke_external_rest_endpoint

The request returns HTTP status code 429.

What should the developer implement?

A. Retry logic with exponential backoff.

B. Change JSON_VALUE to JSON_QUERY.

C. Remove authentication.

D. Disable vector indexing.

Answer: A

Explanation

HTTP 429 means:

Too Many Requests

The service is throttling requests.

Recommended approach:

Retry after a delay.
Use exponential backoff.
Monitor service limits.

Question 20 (Choose TWO)

A developer is designing prompts for a RAG application.

Which TWO practices improve response quality?

A. Include relevant retrieved context.

B. Provide clear instructions about expected responses.

C. Include every database table in every prompt.

D. Remove grounding information.

E. Maximize prompt size regardless of relevance.

Choose TWO answers.

Answers:

✅ A
✅ B

Explanation

Effective prompts:

Provide relevant context.
Clearly define expected behavior.

Large amounts of irrelevant information can reduce response quality and increase cost.

Question 21 (Scenario-Based)

A company stores product descriptions as vector embeddings in Azure SQL Database.

The search application needs to find products with similar meanings even when users use different words.

Example:

User query:

“waterproof hiking footwear”

Relevant products:

“weather-resistant trail boots”

Which similarity approach should be used?

A. Exact string comparison

B. Vector similarity search

C. Foreign key lookup

D. Transaction log analysis

Answer: B

Explanation

Vector similarity search compares numerical representations of meaning rather than exact words.

Embeddings allow the system to identify semantic relationships between concepts.

Incorrect:

String comparison requires exact matches.
Foreign keys are relational integrity features.
Transaction logs are unrelated to search.

Question 22 (Choose TWO)

A developer is evaluating vector search performance.

Which TWO metrics are important when assessing a vector search implementation?

A. Search relevance

B. Number of database users

C. Query latency

D. Stored procedure naming conventions

E. Database object ownership

Choose TWO answers.

Answers:

✅ A
✅ C

Explanation

Vector search performance is evaluated using:

Search relevance

Measures whether returned results are meaningful.

Query latency

Measures how quickly results are returned.

Other options do not measure vector search quality.

Question 23 (Scenario-Based)

A company implements hybrid search.

The keyword search engine returns:

Rank	Document
1	Document A
2	Document B

The vector search engine returns:

Rank	Document
1	Document C
2	Document A

The company wants to combine rankings without comparing incompatible relevance scores.

Which technique should be used?

A. Reciprocal Rank Fusion

B. Database normalization

C. Index fragmentation

D. Data compression

Answer: A

Explanation

Reciprocal Rank Fusion (RRF):

Combines ranked lists.
Does not require score normalization.
Improves hybrid search results.

RRF assigns higher weight to documents appearing near the top of multiple rankings.

Question 24 (Matching)

Match each search technology with its best use case.

Technology	Use Case
1. Keyword search	A. Finding similar concepts
2. Vector search	B. Exact identifiers
3. Hybrid search	C. Combining semantic and lexical retrieval

Answer

Technology	Match
Keyword search	B
Vector search	A
Hybrid search	C

Explanation

Keyword search:

Best for exact terms.
Example: product numbers, policy IDs.

Vector search:

Best for semantic similarity.

Hybrid search:

Combines both approaches.

Question 25 (Ordering)

Arrange the steps for implementing a RAG solution.

Generate embeddings for source documents.
Retrieve relevant documents using search.
Store embeddings in a vector-enabled database.
Add retrieved information to the prompt.
Send the prompt to the language model.

Correct Order:

1 → 3 → 2 → 4 → 5

Explanation

A typical RAG workflow:

Step 1

Documents are converted into embeddings.

Step 2

Embeddings are stored.

Step 3

A user query retrieves similar content.

Step 4

Retrieved information is added to the prompt.

Step 5

The language model generates a response.

Question 26 (Scenario-Based)

A developer creates a RAG chatbot.

Users complain that answers contain information that is not in company documents.

Which improvement should be implemented?

A. Add stronger grounding instructions in the prompt.

B. Increase the temperature value.

C. Remove retrieved documents from the prompt.

D. Increase database transaction isolation.

Answer: A

Explanation

This problem is called hallucination.

Reducing hallucination requires:

Better grounding.
Clear prompt instructions.
Restricting responses to retrieved information.

Example:

“Answer only using the provided documents.”

Question 27 (Single Answer)

A developer receives this response from an AI service:

			
{
 "response": {
   "text": "Your request was approved."
 }
}

		

Which SQL function should be used to retrieve the nested text value?

A. JSON_VALUE

B. JSON_QUERY

C. OPENXML

D. STRING_SPLIT

Answer: A

Explanation

JSON_VALUE extracts scalar values.

Example:

			
SELECT JSON_VALUE(
    @json,
    '$.response.text'
);

Other functions:

JSON_QUERY returns JSON objects or arrays.
STRING_SPLIT separates text values.
OPENXML is an XML function.

Question 28 (Choose THREE)

A company is designing an enterprise RAG application.

Which THREE design considerations should be implemented?

A. Chunk documents into meaningful sections.

B. Generate embeddings using an appropriate model.

C. Store retrieved documents with metadata.

D. Include every document in every prompt.

E. Ignore document updates.

Choose THREE answers.

Answers:

✅ A
✅ B
✅ C

Explanation

High-quality RAG systems require:

Chunking

Improves retrieval precision.

Embeddings

Enable semantic matching.

Metadata

Supports filtering and ranking.

Incorrect:

Sending all documents increases cost and reduces relevance.
Updated documents require embedding refreshes.

Question 29 (Scenario-Based)

A developer creates an AI-enabled SQL application.

The application sends customer information to an external language model.

The company requires that only necessary information is shared.

What should the developer implement?

A. Data minimization before sending prompts.

B. Disable encryption.

C. Store API keys in application code.

D. Send the entire database schema.

Answer: A

Explanation

Data minimization is a key AI security practice.

The application should:

Send only required information.
Remove unnecessary sensitive data.
Protect customer privacy.

Incorrect:

API keys should not be embedded in code.
Encryption should not be disabled.
Sending unnecessary data increases risk.

Question 30 (Comprehensive Scenario-Based)

A company is building an AI-powered knowledge assistant using Azure SQL Database.

Requirements:

Users ask natural language questions.
The assistant must answer using company documents.
Exact product codes must be searchable.
Similar concepts must also be discovered.
Documents are updated frequently.
The AI model should not require retraining.

Which architecture should be implemented?

A. Traditional relational queries only.

B. Fine-tune the language model every time documents change.

C. RAG using embeddings, hybrid search, and prompt augmentation.

D. Export all company data directly into the language model.

Answer: C

Explanation

The correct architecture is a Retrieval-Augmented Generation (RAG) solution.

The design should include:

Data preparation

Split documents into chunks.
Generate embeddings.
Store vectors.

Search

Use vector search for semantic matching.
Use keyword search for exact values.
Combine results using hybrid search and RRF.

Generation

Add retrieved context to prompts.
Send grounded prompts to the language model.

Benefits:

✅ Updated information without retraining
✅ Better accuracy
✅ Reduced hallucinations
✅ Supports enterprise search scenarios

Go to the DP-800 Exam Prep Hub main page

AI, Data Development, Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Exam Prep Hub for DP-800: Developing AI-Enabled Database Solutions

Welcome to the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub!

Welcome to the one-stop hub with information for preparing for the DP-800: Developing AI-Enabled Database Solutions certification exam. The content for this exam helps prepare you to have “subject matter expertise in designing and developing AI-enabled database solutions across Microsoft SQL platforms, including Microsoft SQL Server, Azure SQL, and SQL databases in Microsoft Fabric”.
Upon successful completion of the exam, you earn the Microsoft Certified: SQL AI Developer Associate certification.

This hub provides information directly here (topic-by-topic as outlined in the official study guide), links to a number of external resources, tips for preparing for the exam, practice tests, and section questions to help you prepare. Bookmark this page and use it as a guide to ensure that you are fully covering all relevant topics for the DP-800 exam and making use of as many of the resources available as possible.

Audience profile (from Microsoft’s site)

As a candidate for this Microsoft Certification, you should have subject matter expertise in designing and developing AI-enabled database solutions across Microsoft SQL platforms, including Microsoft SQL Server, Azure SQL, and SQL databases in Microsoft Fabric.

You should also have experience writing T-SQL code and developing databases in Microsoft SQL platforms. Plus, you need to be familiar with continuous integration and continuous deployment (CI/CD) practices in GitHub, AI-assisted development tools, and AI concepts, such as embeddings, vectors, and models.

Your responsibilities include:
- Designing and developing database solutions that include both structured and semi-structured data.
- Integrating AI features into modern and highly scalable enterprise applications.
- Securing, optimizing, and deploying database solutions.
- Implementing AI capabilities in database solutions.

You work closely with application developers; database administrators (DBAs); architects; AI engineers; development, security, operations (DevSecOps) engineers; security and compliance administrators; and other stakeholders to deliver robust, high-performance database solutions that power modern applications and AI-driven experiences.

Skills at a glance (as specified in the official study guide)

Design and develop database solutions (35–40%)
Secure, optimize, and deploy database solutions (35–40%)
Implement AI capabilities in database solutions (25–30%)

Topic-by-Topic Exam Content

[click a topic link to access the content and practice questions for that topic]

Design and develop database solutions (35–40%)

Design and implement database objects

Implement programmability objects

Write advanced T-SQL code

Design and implement SQL solutions by using AI-assisted tools

Interpret security impact of using AI-assisted tools
Enable GitHub Copilot and Microsoft Copilot in Fabric
Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session
Create and configure GitHub Copilot instruction files
Connect to MCP server endpoints, including Microsoft SQL Server and Fabric lakehouse

Secure, optimize, and deploy database solutions (35–40%)

Implement data security and compliance

Optimize database performance

Implement CI/CD by using SQL Database Projects

Design and implement a testing strategy, including unit tests and integration tests
Create and manage reference/static data in source control
Create, build, and validate database models by using SQL Database Projects, including SDK-style models
- Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 1
- Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 2
Configure source control for SQL Database Projects
Manage branching, pull requests, and conflict resolution
Implement secrets management
Detect schema drift by using SQL Database Projects
Update a SQL database project and deploy changes
Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners

Integrate SQL solutions with Azure services

Implement AI capabilities in database solutions (25–30%)

Design and implement models and embeddings

Evaluate external models, including multimodal, multilanguage, sizes, and structured output
Create and manage external models
Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry
- Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry – Part 1
- Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry – Part 2
Identify which columns to include in embeddings
Design and implement chunks for embeddings
Generate embeddings

Design and implement intelligent search

Design and implement retrieval-augmented generation (RAG)

DP-800 Practice Exams

Important DP-800 Resources

Link to the free, comprehensive, self-paced course on Microsoft Learn:
Course: Develop AI-enabled database solutions

Course DP-800T00-A: Develop AI-enabled database solutions – Training | Microsoft Learn

This course has 3 learning paths. The 3 learning paths and their modules are listed with links below:

(1) Design and develop database solutions

This learning path has 4 modules:
(i) Design and implement database objects with SQL
(ii) Implement programmability objects with SQL
(iii) Write advanced T-SQL code
(iv) Implement SQL solutions by using AI-assisted tools

(2) Secure, optimize, and deploy database solutions

This learning path has 4 modules:
(i) Implement data security and compliance with SQL
(ii) Optimize database performance
(iii) Implement CI/CD by using SQL Database Projects
(iv) Integrate SQL solutions with Azure services

(3) Implement AI capabilities in database solutions

This learning path has 3 modules:
(i) Design and implement models and embeddings with SQL
(ii) Design and implement intelligent search with SQL
(iii) Design and implement RAG with SQL

Courses:

These are two highly rated courses for DP-800 on Udemy:

Good luck to you passing the DP-800 Exam!
However, the more preparation you have, the less luck you will need. 🙂

Visit this post to see the list of all the certification preparation hubs available on The Data Community.