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.

Answer

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.

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

Correct Order

1 → 2 → 4 → 3

Explanation

The typical workflow is:

1. Commit changes to source control.
2. Validate the project during the build.
3. Generate the deployment artifact (such as a DACPAC).
4. 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.

Answer

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:

1. Source information.
2. Embeddings to represent semantic meaning.
3. 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.

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

Correct Order

2 → 3 → 4 → 1

Explanation

A RAG workflow follows these steps:

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

Question 26 (Match the Answers)

Match each technology with its purpose.

Answer

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?

A.

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

B.

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

C.

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

D.

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:

1. Generate embeddings for documents.
2. Store embeddings in a vector-enabled database.
3. Perform hybrid search:
   • Keyword search for exact matches.
   • Vector search for semantic similarity.
4. Add retrieved context to the prompt.
5. 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