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

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