DP-800 Practice Questions & Answers • ExamPrepare

Question 1

Existing Environment

Azure Environment

Table names	Column names
CustomerFeedback	FeedbackId (int) (primarykey) FeedbackJson (nvarchar (max))
Fleets	FleetId (int) (primarykey) FleetName (nvarchar(100)) Description (nvarchar(256))
MaintenanceEvents	MaintenanceId (int) (primarykey) VehicleId (int) LastModifiedUTC (datetime2) Description (nvarchar(256))
SupportTickets	TicketId (int) (primarykey) FleetId (int) CreatedUtc (datetime2)
UserAccounts	UserId (int) (primarykey) UserPrincipalName (nvarchar(256)) JobRole (nvarchar(256)) StartDate (datetime2)
VehicleIncidentReports	IncidentId (int) (primarykey) VehicleId (int) FleetId (int) IncidentType (nvarchar(50)) VehicleLocation (nvarchar(200)) IncidentDescription (nvarchar(max)) SeverityScore (int)
Vehicles	VehicleId (int) (primarykey) VIN ((nvarchar(50)) VehicleDescription (nvarchar(256))
VehicleHealthSummary	VehicleId (int) (primarykey) FleetId (int) Summary (nvarchar(2000)) LastUpdatedUtc (datetime2) EngineStatus [bit] EngineStatusLastUpdatedUtc (datetime2) BatteryHealth (int) Embeddings (vector (1536))

FeedbackJson { "text": "The battery drains too fast when driving uphill.", "category": "Battery", "metadata": { "appVersion": "5.2.1", "device": "Android", "language": "en-GB" } } UNMASK

Problem Statements

AI workloads
Vector search
Modernized API access
Retrieval Augmented Generation (RAG) pipelines

SELECT VehicleId, LastUpdatedUtc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSummary WHERE FleetId = @FleetId ORDER BY LastUpdatedUtc DESC; VehicleIncidentReports

Requirements

Planned Changes

Security Requirements

Restrict non-privileged users from viewing Personally Identifiable Information (PII) data, which is full email addresses and phone numbers.
Enforce row-level filtering so that analysts see only incidents for the fleets to which they are assigned. The analysts can be assigned to multiple fleets.

Database Performance Requirements

Telemetry data must be stored in a partitioned table.
Telemetry data must provide predictable performance for ingestion and retention operations.
latitude, longitude, and accuracy JSON properties must be filtered by using an index seek.

Ensure that any changes to a row in the MaintenanceEvents table updates the corresponding value in the LastModifiedUtc column to the time of the change.
Avoid recursive updates.

AI Search, Embeddings, and Vector Indexing

Embeddings must be stored in dedicated Azure SQL Database tables.
Embeddings must be generated from rich natural language fields.
Chunking must preserve semantic coherence.
Hybrid search must combine the following:
- Vector similarity
- Keyword filtering or boosting

Development Requirements

FeedbackJson CustomerFeedback

Extract the customer feedback text from the JSON document.
Filter rows where the JSON text contains a keyword.
Calculate a fuzzy similarity score between the feedback text and a known issue description.
Order the results by similarity score, with the highest score first.

You need to recommend a solution for the development team to retrieve the live metadata. The solution must meet the development requirements.

What should you include in the recommendation?

Use an MCP server.
Export the database schema as a .dacpac file and load the schema into a GitHub Copilot context window.
Add the schema to a GitHub Copilot instruction file.
Include the database project in the code repository.

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 2

Existing Environment

Azure Environment

Table names	Column names
CustomerFeedback	FeedbackId (int) (primarykey) FeedbackJson (nvarchar (max))
Fleets	FleetId (int) (primarykey) FleetName (nvarchar(100)) Description (nvarchar(256))
MaintenanceEvents	MaintenanceId (int) (primarykey) VehicleId (int) LastModifiedUTC (datetime2) Description (nvarchar(256))
SupportTickets	TicketId (int) (primarykey) FleetId (int) CreatedUtc (datetime2)
UserAccounts	UserId (int) (primarykey) UserPrincipalName (nvarchar(256)) JobRole (nvarchar(256)) StartDate (datetime2)
VehicleIncidentReports	IncidentId (int) (primarykey) VehicleId (int) FleetId (int) IncidentType (nvarchar(50)) VehicleLocation (nvarchar(200)) IncidentDescription (nvarchar(max)) SeverityScore (int)
Vehicles	VehicleId (int) (primarykey) VIN ((nvarchar(50)) VehicleDescription (nvarchar(256))
VehicleHealthSummary	VehicleId (int) (primarykey) FleetId (int) Summary (nvarchar(2000)) LastUpdatedUtc (datetime2) EngineStatus [bit] EngineStatusLastUpdatedUtc (datetime2) BatteryHealth (int) Embeddings (vector (1536))

FeedbackJson { "text": "The battery drains too fast when driving uphill.", "category": "Battery", "metadata": { "appVersion": "5.2.1", "device": "Android", "language": "en-GB" } } UNMASK

Problem Statements

AI workloads
Vector search
Modernized API access
Retrieval Augmented Generation (RAG) pipelines

SELECT VehicleId, LastUpdatedUtc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSummary WHERE FleetId = @FleetId ORDER BY LastUpdatedUtc DESC; VehicleIncidentReports

Requirements

Planned Changes

Security Requirements

Restrict non-privileged users from viewing Personally Identifiable Information (PII) data, which is full email addresses and phone numbers.
Enforce row-level filtering so that analysts see only incidents for the fleets to which they are assigned. The analysts can be assigned to multiple fleets.

Database Performance Requirements

Telemetry data must be stored in a partitioned table.
Telemetry data must provide predictable performance for ingestion and retention operations.
latitude, longitude, and accuracy JSON properties must be filtered by using an index seek.

Ensure that any changes to a row in the MaintenanceEvents table updates the corresponding value in the LastModifiedUtc column to the time of the change.
Avoid recursive updates.

AI Search, Embeddings, and Vector Indexing

Embeddings must be stored in dedicated Azure SQL Database tables.
Embeddings must be generated from rich natural language fields.
Chunking must preserve semantic coherence.
Hybrid search must combine the following:
- Vector similarity
- Keyword filtering or boosting

Development Requirements

FeedbackJson CustomerFeedback

Extract the customer feedback text from the JSON document.
Filter rows where the JSON text contains a keyword.
Calculate a fuzzy similarity score between the feedback text and a known issue description.
Order the results by similarity score, with the highest score first.

You need to recommend a solution that will resolve the ingestion pipeline failure issues.

Which two actions should you recommend? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

Add foreign key constraints on the table.
Add a check constraint that validates the JSON structure.
Create a unique index on a hash of the payload.
Enable snapshot isolation on the database.
Use a trigger to automatically rewrite malformed JSON.

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 3

Existing Environment

Azure Environment

Table names	Column names
CustomerFeedback	FeedbackId (int) (primarykey) FeedbackJson (nvarchar (max))
Fleets	FleetId (int) (primarykey) FleetName (nvarchar(100)) Description (nvarchar(256))
MaintenanceEvents	MaintenanceId (int) (primarykey) VehicleId (int) LastModifiedUTC (datetime2) Description (nvarchar(256))
SupportTickets	TicketId (int) (primarykey) FleetId (int) CreatedUtc (datetime2)
UserAccounts	UserId (int) (primarykey) UserPrincipalName (nvarchar(256)) JobRole (nvarchar(256)) StartDate (datetime2)
VehicleIncidentReports	IncidentId (int) (primarykey) VehicleId (int) FleetId (int) IncidentType (nvarchar(50)) VehicleLocation (nvarchar(200)) IncidentDescription (nvarchar(max)) SeverityScore (int)
Vehicles	VehicleId (int) (primarykey) VIN ((nvarchar(50)) VehicleDescription (nvarchar(256))
VehicleHealthSummary	VehicleId (int) (primarykey) FleetId (int) Summary (nvarchar(2000)) LastUpdatedUtc (datetime2) EngineStatus [bit] EngineStatusLastUpdatedUtc (datetime2) BatteryHealth (int) Embeddings (vector (1536))

FeedbackJson { "text": "The battery drains too fast when driving uphill.", "category": "Battery", "metadata": { "appVersion": "5.2.1", "device": "Android", "language": "en-GB" } } UNMASK

Problem Statements

AI workloads
Vector search
Modernized API access
Retrieval Augmented Generation (RAG) pipelines

SELECT VehicleId, LastUpdatedUtc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSummary WHERE FleetId = @FleetId ORDER BY LastUpdatedUtc DESC; VehicleIncidentReports

Requirements

Planned Changes

Security Requirements

Restrict non-privileged users from viewing Personally Identifiable Information (PII) data, which is full email addresses and phone numbers.
Enforce row-level filtering so that analysts see only incidents for the fleets to which they are assigned. The analysts can be assigned to multiple fleets.

Database Performance Requirements

Telemetry data must be stored in a partitioned table.
Telemetry data must provide predictable performance for ingestion and retention operations.
latitude, longitude, and accuracy JSON properties must be filtered by using an index seek.

Ensure that any changes to a row in the MaintenanceEvents table updates the corresponding value in the LastModifiedUtc column to the time of the change.
Avoid recursive updates.

AI Search, Embeddings, and Vector Indexing

Embeddings must be stored in dedicated Azure SQL Database tables.
Embeddings must be generated from rich natural language fields.
Chunking must preserve semantic coherence.
Hybrid search must combine the following:
- Vector similarity
- Keyword filtering or boosting

Development Requirements

FeedbackJson CustomerFeedback

Extract the customer feedback text from the JSON document.
Filter rows where the JSON text contains a keyword.
Calculate a fuzzy similarity score between the feedback text and a known issue description.
Order the results by similarity score, with the highest score first.

You need to recommend a solution to resolve the slow dashboard query issue.

What should you recommend?

On FleetId , create a nonclustered index that includes LastUpdatedUtc , EngineStatus , and BatteryHealth .
Create a clustered index on LastUpdatedUtc .
On FleetId , create a filtered index where LastUpdatedUtc > DATEADD( DAY, -7, SYSUTCDATETIME( )) .
On LastUpdatedUtc , create a nonclustered index that includes FleetId .

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 4

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders. dbo.Orders has a clustered index, contains three years of data, and is partitioned by a column named OrderDate by month.

You need to remove all the rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.Orders.

Solution: Identify the partition number for the oldest month, and then run the following Transact-SQL statement.

TRUNCATE TABLE dbo.Orders
WITH (PARTITIONS (partition number));

Does this meet the goal?

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 5

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders. dbo.Orders has a clustered index, contains three years of data, and is partitioned by a column named OrderDate by month.

You need to remove all the rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.Orders.

Solution: Identify the partition scheme for the oldest month, and then run the following Transact-SQL statement.

ALTER TABLE dbo.Orders
DROP PARTITION SCHEME (partition_scheme_name);

Does this meet the goal?

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 6

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders. dbo.Orders has a clustered index, contains three years of data, and is partitioned by a column named OrderDate by month.

You need to remove all the rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.Orders.

Solution: Run the following Transact-SQL statement.

TRUNCATE TABLE dbo.Orders;

Does this meet the goal?

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.