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:

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.

Answer

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:

1. Converting content into embeddings.
2. Storing those vectors.
3. 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:

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.

Answer

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:

The vector search engine returns:

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.

Answer

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.

1. Generate embeddings for source documents.
2. Retrieve relevant documents using search.
3. Store embeddings in a vector-enabled database.
4. Add retrieved information to the prompt.
5. 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