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

Question 1 (Scenario-Based)

A retail company is designing a SQL database for order processing.

Requirements:

• Orders are frequently retrieved by OrderID.
• New orders are inserted continuously.
• The database supports thousands of transactions per minute.
• Reports are generated against a separate analytical system.

Which indexing strategy should you recommend for the Orders table?

A. Use a clustered index on OrderID.

B. Use a clustered columnstore index.

C. Create nonclustered indexes on every column.

D. Store order data as JSON documents.

Answer: A

Explanation

An OLTP order-processing system benefits from a clustered index on the primary transaction key.

A clustered index:

• Provides efficient lookups.
• Supports frequent inserts and updates.
• Organizes table storage.

Why the others are incorrect:

• B: Clustered columnstore indexes are optimized for analytics, not high-volume OLTP.
• C: Excessive indexes increase insert/update overhead.
• D: JSON storage is not appropriate for a highly relational transaction workload.

Question 2 (Choose TWO)

A developer is creating a reusable database API layer using stored procedures.

Which TWO practices should be implemented?

A. Use input parameters instead of string concatenation.

B. Grant applications direct access to all tables.

C. Include error handling with TRY…CATCH.

D. Store passwords in stored procedure code.

E. Disable transaction handling.

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

Stored procedures should:

• Accept parameters to reduce SQL injection risks.
• Include error handling to gracefully manage failures.

The other choices introduce security or reliability issues.

Question 3 (Single Answer)

A developer needs to create a database object that automatically returns only rows belonging to the current user.

Which SQL feature should be used?

A. Dynamic Data Masking

B. Row-Level Security

C. Transparent Data Encryption

D. Columnstore Index

Answer: B

Explanation

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

Example:

A salesperson should only see customers assigned to their territory.

Why the others are incorrect:

• Dynamic Data Masking hides values but does not filter rows.
• TDE encrypts data at rest.
• Columnstore indexes improve analytics performance.

Question 4 (Fill in the Blank)

Complete the statement.

The SQL Server feature that stores historical query execution information and helps identify performance regressions is:

A. Query Store

B. Resource Governor

C. SQL Server Agent

D. Database Mail

Answer: A

Explanation

Query Store captures:

• Query text
• Execution plans
• Runtime statistics

It is commonly used after deployments to determine whether performance has degraded.

Question 5 (Scenario-Based)

A company has a customer table containing:

• CustomerID
• Name
• EmailAddress
• PhoneNumber

Customer service representatives need to search customers by name and email.

The application frequently executes:

SELECT *
FROM Customers
WHERE EmailAddress = @Email;

The query is slow.

What should you implement?

A. A nonclustered index on EmailAddress.

B. A clustered columnstore index.

C. Database encryption.

D. A JSON document store.

Answer: A

Explanation

A nonclustered index on EmailAddress allows efficient searches using the predicate.

The query optimizer can perform an index seek instead of scanning the entire table.

Question 6 (Matching)

Match each SQL feature with its purpose.

Answer

Explanation

• DDM masks sensitive data.
• TDE protects stored database files.
• RLS restricts row visibility.

Question 7 (Scenario-Based)

A company wants to deploy schema changes automatically.

The development team uses Visual Studio SQL Database Projects.

The deployment process must:

• Detect schema conflicts before deployment.
• Store database changes in source control.
• Deploy only approved changes.

Which solution should be implemented?

A. Manually copy SQL scripts to production.

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

C. Allow developers to modify production directly.

D. Disable schema validation.

Answer: B

Explanation

SQL Database Projects integrate well with CI/CD pipelines.

Benefits include:

• Version control.
• Automated builds.
• Schema validation.
• Repeatable deployments.

Question 8 (Choose THREE)

A developer is designing a vector search solution.

Which THREE practices improve search quality?

A. Generate embeddings using a suitable AI model.

B. Select an appropriate similarity metric.

C. Split large documents into meaningful chunks.

D. Convert embeddings into XML.

E. Disable indexing.

Choose THREE answers.

Answers

✅ A

✅ B

✅ C

Explanation

Vector search quality depends on:

• Good embeddings.
• Proper chunking.
• Appropriate similarity calculations.

Converting vectors to XML or disabling indexes reduces effectiveness.

Question 9 (Scenario-Based)

A developer implements a Retrieval-Augmented Generation application.

The application workflow is:

1. User asks a question.
2. The system searches documents.
3. Relevant documents are added to the prompt.
4. The language model generates an answer.

Users report incorrect answers when documents contain outdated information.

What should be improved?

A. Increase the language model temperature.

B. Ensure document updates regenerate embeddings and refresh the vector index.

C. Remove retrieval from the architecture.

D. Increase SQL transaction isolation.

Answer: B

Explanation

RAG depends on accurate retrieval.

When documents change:

1. Generate new embeddings.
2. Update vector storage.
3. Ensure the search index contains current data.

Question 10 (Single Answer)

A developer wants to retrieve a scalar value from JSON returned by an AI service.

Which SQL function should be used?

A. OPENJSON

B. JSON_QUERY

C. JSON_VALUE

D. FOR JSON PATH

Answer: C

Explanation

JSON_VALUE extracts a single scalar value.

Example:

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

Other functions:

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

Question 11 (Scenario-Based)

A company has an application that retrieves customer order history.

The following query is executed frequently:

SELECT OrderDate, Amount
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderDate DESC;

The query is slow because the Orders table contains several hundred million rows.

Which index should you create?

A. A nonclustered index on CustomerID that includes OrderDate and Amount.

B. A clustered columnstore index on the Orders table.

C. A unique constraint on OrderDate.

D. A full-text index on CustomerID.

Answer: A

Explanation

A covering nonclustered index improves this query because:

• CustomerID is used for filtering.
• OrderDate supports sorting.
• Amount is included to avoid additional lookups.

A clustered columnstore index is better suited for analytical workloads.

Question 12 (Choose TWO)

A database developer is writing complex T-SQL code.

Which TWO practices improve maintainability and performance?

A. Use Common Table Expressions (CTEs) when appropriate.

B. Avoid all stored procedures.

C. Use execution plans to analyze queries.

D. Store all business logic in application code.

E. Ignore query statistics.

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

Good T-SQL practices include:

• Using CTEs to simplify complex queries.
• Reviewing execution plans to identify bottlenecks.

Execution plans reveal:

• Table scans
• Missing indexes
• Expensive operators

Question 13 (Single Answer)

A company wants to prevent users from viewing sensitive information while allowing applications to access the original values securely.

Which feature should be considered?

A. Dynamic Data Masking

B. Always Encrypted

C. Columnstore Index

D. Query Store

Answer: B

Explanation

Always Encrypted protects sensitive data from being exposed to database administrators or unauthorized users.

Encryption and decryption occur outside the database engine.

Comparison:

• DDM masks values but privileged users can access original data.
• TDE protects data at rest.
• Query Store tracks performance.

Question 14 (Scenario-Based)

A developer creates a stored procedure that dynamically builds SQL statements:

SET @sql = 
'SELECT * FROM Customers WHERE Name = ''' 
+ @Name + '''';
EXEC(@sql);

Security testing identifies a SQL injection vulnerability.

What should the developer do?

A. Use parameterized SQL with sp_executesql.

B. Encrypt the database.

C. Add more indexes.

D. Enable Query Store.

Answer: A

Explanation

Dynamic SQL that concatenates user input can allow SQL injection.

Using:

sp_executesql

with parameters separates:

• SQL code
• User-provided values

This improves security and query plan reuse.

Question 15 (Fill in the Blank)

Complete the statement.

A vector search system stores numerical representations of data called __________ that capture semantic meaning.

A. Tokens

B. Embeddings

C. Transactions

D. Partitions

Answer: B

Explanation

Embeddings are numerical vectors generated by AI models.

They allow systems to compare similarity between:

• Documents
• Images
• Questions
• Other data types

Question 16 (Matching)

Match each Azure SQL AI capability with its purpose.

Answer

Explanation

The components work together:

1. Embeddings convert data into vectors.
2. Vector search finds similar vectors.
3. Language models generate responses.

Question 17 (Choose THREE)

A company is optimizing an Azure SQL Database workload.

Which THREE actions can improve query performance?

A. Create appropriate indexes.

B. Review execution plans.

C. Update outdated statistics.

D. Disable all constraints.

E. Remove all indexes.

Choose THREE answers.

Answers

✅ A

✅ B

✅ C

Explanation

Performance optimization commonly includes:

• Proper indexing.
• Execution plan analysis.
• Maintaining statistics.

Removing indexes or disabling constraints generally reduces database quality.

Question 18 (Scenario-Based)

A company wants to create an AI assistant that answers employee questions about internal policies.

The company wants answers based only on approved documents.

Which architecture should be implemented?

A. Store all documents directly inside the language model.

B. Use Retrieval-Augmented Generation with document retrieval.

C. Increase the model temperature.

D. Remove document indexing.

Answer: B

Explanation

RAG provides:

• External knowledge retrieval.
• Grounded prompts.
• Updated information without retraining.

The model receives relevant documents as context when generating responses.

Question 19 (Scenario-Based)

A developer implements hybrid search.

The system performs:

• Keyword search using SQL full-text capabilities.
• Vector similarity search using embeddings.

The developer needs to merge both ranked result sets.

Which technique should be used?

A. Reciprocal Rank Fusion

B. Data compression

C. Database normalization

D. Horizontal partitioning

Answer: A

Explanation

Reciprocal Rank Fusion (RRF):

• Combines multiple ranked lists.
• Does not require matching score scales.
• Improves hybrid search relevance.

Question 20 (Choose TWO)

A developer sends SQL data to an external language model.

Which TWO practices should be followed?

A. Remove unnecessary sensitive information before sending prompts.

B. Include all database columns to maximize context.

C. Validate and sanitize generated responses.

D. Disable access controls for AI applications.

E. Store API keys directly in application code.

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

Secure AI implementations should:

• Minimize data exposure.
• Validate AI outputs.

Avoid:

• Sending unnecessary data.
• Hardcoding secrets.
• Removing security controls.

Question 21 (Scenario-Based)

A development team is building an AI-powered customer support assistant.

The architecture includes:

• Azure SQL Database containing product documentation
• Vector embeddings stored with documents
• A language model generating answers

Users report that the assistant provides inaccurate answers when documents are updated.

What should the development team implement?

A. Increase the language model temperature.

B. Regenerate embeddings and update the vector index whenever documents change.

C. Increase the size of the language model.

D. Remove vector search and use keyword search only.

Answer: B

Explanation

In a RAG solution, document updates require:

1. Updating the source data.
2. Regenerating embeddings.
3. Updating the vector index.

The language model does not automatically learn from updated documents.

Question 22 (Choose TWO)

A developer is designing a vector search implementation.

Which TWO factors should be evaluated when choosing a vector index strategy?

A. Number of vectors stored.

B. Database user roles.

C. Search latency requirements.

D. Column naming conventions.

E. Stored procedure naming standards.

Choose TWO answers.

Answers

✅ A

✅ C

Explanation

Vector search performance depends on:

• The size of the vector collection.
• Required query response times.

Other factors listed do not directly affect vector index selection.

Question 23 (Single Answer)

A developer wants to compare the meaning of two pieces of text instead of comparing exact words.

Which approach should be used?

A. String comparison functions

B. Vector embeddings with similarity search

C. Database triggers

D. Data compression

Answer: B

Explanation

Vector embeddings represent semantic meaning numerically.

Similarity searches can identify related concepts even when the wording differs.

Example:

“automobile repair”

and

“vehicle maintenance”

may be considered similar.

Question 24 (Scenario-Based)

A company creates an AI assistant that uses RAG.

The prompt sent to the language model contains:

• User question
• Retrieved documents
• Instructions

The developer wants the model to answer only from retrieved documents.

Which prompt design approach should be used?

A. Tell the model to use provided context and avoid unsupported answers.

B. Remove retrieved documents from the prompt.

C. Increase randomness using a higher temperature.

D. Include unrelated database information.

Answer: A

Explanation

A well-designed RAG prompt should:

• Provide relevant context.
• Define response rules.
• Reduce hallucinations.

Example instruction:

“Answer only using the supplied documents. If the answer is not present, state that you do not know.”

Question 25 (Ordering)

Arrange the following steps for creating a RAG application.

1. Generate embeddings for documents.
2. Retrieve relevant documents.
3. Store document embeddings.
4. Send augmented prompt to the language model.
5. Combine retrieved content with the user question.

Correct Order

1 → 3 → 2 → 5 → 4

Explanation

A typical RAG workflow:

1. Convert documents into embeddings.
2. Store vectors in a vector-enabled database.
3. Search vectors when a user asks a question.
4. Add retrieved content to the prompt.
5. Send the prompt to the language model.

Question 26 (Matching)

Match each SQL JSON function with its purpose.

Answer

Explanation

SQL Server JSON functions:

JSON_VALUE

• Retrieves a single scalar value.

JSON_QUERY

• Retrieves JSON objects or arrays.

OPENJSON

• Converts JSON data into relational rows.

Question 27 (Choose THREE)

A company is preparing an enterprise AI application using Azure SQL Database.

Which THREE security practices should be implemented?

A. Use managed identities where possible.

B. Apply least-privilege database permissions.

C. Log and monitor AI application activity.

D. Store API keys in source code.

E. Send all customer data to the model.

Choose THREE answers.

Answers

✅ A

✅ B

✅ C

Explanation

Enterprise AI applications should follow security best practices:

• Managed identities reduce credential exposure.
• Least privilege limits access.
• Monitoring supports auditing and governance.

Avoid:

• Hardcoded credentials.
• Sending unnecessary sensitive information.

Question 28 (Scenario-Based)

A developer uses:

EXEC sp_invoke_external_rest_endpoint

to call an external AI service.

The request fails with HTTP status code 401.

What is the most likely issue?

A. The AI service returned too many results.

B. Authentication credentials are missing or invalid.

C. The database has insufficient storage.

D. The prompt contains too many tokens.

Answer: B

Explanation

HTTP 401 means:

Unauthorized

Common causes:

• Missing authentication headers.
• Invalid credentials.
• Expired tokens.

Other common HTTP codes:

• 400 = Bad request.
• 429 = Too many requests.
• 500 = Server error.

Question 29 (Scenario-Based)

A company has implemented hybrid search.

The results from keyword search and vector search have different scoring systems.

The team needs to combine the results without manually normalizing scores.

Which approach should be used?

A. Reciprocal Rank Fusion

B. Increase vector dimensions

C. Remove keyword search

D. Use a larger database transaction log

Answer: A

Explanation

Reciprocal Rank Fusion (RRF):

• Combines ranked lists.
• Works even when scoring methods differ.
• Improves hybrid search relevance.

Example:

Keyword search ranking:

1. Document A
2. Document B

Vector search ranking:

1. Document C
2. Document A

RRF combines these rankings into a unified result list.

Question 30 (Comprehensive Scenario)

A company is building an AI knowledge assistant.

Requirements:

• Data is stored in Azure SQL Database.
• Users ask questions using natural language.
• The system must find relevant information even when wording differs.
• Exact identifiers such as policy numbers must still work.
• Responses must be based on company documents.
• New documents must be available without retraining the AI model.

Which architecture should be implemented?

A. Traditional SQL queries only.

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

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

D. Export all database content into the language model.

Answer: C

Explanation

The recommended architecture is:

1. Store documents in Azure SQL Database.
2. Generate embeddings.
3. Store embeddings for vector search.
4. Perform hybrid search:
   • Keyword search handles exact terms.
   • Vector search handles semantic meaning.
5. Use RRF to merge rankings.
6. Add retrieved information to the prompt.
7. Generate a grounded response.

Benefits:

• Updated information without model retraining.
• Better accuracy.
• Reduced hallucination.
• Improved search relevance.

Go to the DP-800 Exam Prep Hub main page