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.

Answer

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.

1. Retrieve relevant documents.
2. Call the language model.
3. Generate embeddings for the user question.
4. Construct the prompt.

Correct Order

3 → 1 → 4 → 2

Explanation

The workflow is:

1. Generate an embedding for the user’s question.
2. Retrieve similar documents.
3. Build the prompt using the retrieved context.
4. 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.

Answer

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:

1. Generate embeddings.
2. Store vectors.
3. Create/populate the vector index.
4. 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.

1. Generate embeddings.
2. Store embeddings in the database.
3. Create the vector index.
4. 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.

Answer

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