DP-800 Real Exam Questions and Verified Answers

Case Study 1

Existing Environment

Contoso has an Azure subscription in North Europe that contains the corporate infrastructure. The current infrastructure contains a Microsoft SQL Server 2017 database. The database contains the following tables.

The FeedbackJson column has a full-text index and stores JSON documents in the following format.

The support staff at Contoso never has the unmask permission.

Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;

1. Al workloads
2. Vector search
3. Modernized API access
4. Retrieval Augmented Generation (RAG) pipelines

Sometimes the ingestion pipeline fails due to malformed JSON and duplicate payloads.
The engineers at Contoso report that the following dashboard query runs slowly.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary where
fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;

You review the execution plan and discover that the plan shows a clustered index scan. vehicleincidentReports often contains details about the weather, traffic conditions, and location. Analysts report that it is difficult to find similar incidents based on these details.

Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over incident reports.

Security Requirements
Contoso identifies the following telemetry requirements:
1. Telemetry data must be stored in a partitioned table.
2. Telemetry data must provide predictable performance for ingestion and retention operations.
3. latitude, longitude, and accuracy JSON properties must be filtered by using an index seek.

Contoso identifies the following maintenance data requirements:
1. Ensure that any changes to a row in the MaintenanceEvents table updates the corresponding value in the LastModif reduce column to the time of the change.
2. Avoid recursive updates.

AI Search, Embedding's, and Vector indexing
The development learn at Contoso will use Microsoft Visual Studio Code and GitHub Copilot and will retrieve live metadata from the databases. Contoso identifies the following requirements for querying data in the FeedbackJson column of the customer-Feedback table:
1. Extract the customer feedback text from the JSON document.
2. Filter rows where the JSON text contains a keyword.
3. Calculate a fuzzy similarity score between the feedback text and a known issue description.
4. Order the results by similarity score, with the highest score first.

Question 1 Drag & drop

DRAG DROP

You need to meet the database performance requirements for maintenance data.

How should you complete the Transact-SQL code?

To answer, drag the appropriate values to the correct targets.

Each value may be used once, more than once, or not at all.

You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

Show answer and explanation

Question 2 Multiple choice

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.

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

Show answer and explanation

Correct answers: D, E

Question 3 Single choice

You have an Azure SQL database with Query Store enabled.

After a deployment, one query now has two persisted plans in Query Store. The newer plan has much higher average duration and CPU time than the older plan.

You need to restore the previous performance quickly without changing application code.

Which command should you run?

A
EXEC sys.sp_query_store_set_hints
B
DBCC FREEPROCCACHE
C
EXEC sp_query_store_force_plan
D
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Show answer and explanation

Correct answer: C

Explanation

Option C is correct because sp_query_store_force_plan is used to force a particular plan for a particular query from Query Store, which is the fastest way to return to a known good plan. Options A, B, and D are incorrect because query hints influence compilation rather than directly choosing the already known better plan, while clearing cache is broader and does not guarantee reuse of the preferred historical plan.

Question 4 Single choice

You have a SQL database in Microsoft Fabric that contains a partitioned table named dbo.Orders. The table is partitioned by month on OrderDate.

You need to remove only the oldest month of data while minimizing the impact on other queries.

What should you do?

A
Run DELETE FROM dbo.Orders WHERE OrderDate < @BoundaryDate
B
Run TRUNCATE TABLE dbo.Orders WITH (PARTITIONS (partition_number))
C
Rebuild all indexes and then delete the rows
D
Drop and recreate the table without the old data

Show answer and explanation

Correct answer: B

Explanation

Option B is correct because Microsoft documents that partitioned tables can be maintained efficiently at the partition level, including truncating data in a single partition. TRUNCATE TABLE ... WITH (PARTITIONS (...)) is the low-impact retention operation for this design, assuming the table and indexes are aligned.
Options A, C, and D are incorrect because they introduce unnecessary row-by-row work or excessive disruption compared to partition-level truncation.

Question 5 Single choice

You need to implement a hybrid query in Azure SQL that combines:

vector similarity over an Embedding column keyword relevance over a Description column

The final ranking formula must use the vector distance and the full-text ranking value returned by the keyword search.

Which full-text function should you use for the keyword portion?

A
CONTAINS
B
FREETEXT
C
CONTAINSTABLE
D
LIKE

Show answer and explanation

Correct answer: C

Explanation

Option C is correct because CONTAINSTABLE returns a table that includes a RANK column, and that rank value can be incorporated into a hybrid scoring formula. Microsoft documents that the RANK value ranges from 0 through 1000. Options A, B, and D are incorrect because they don't return the tabular RANK output needed for the weighted formula described in the scenario.

Question 6 Single choice

You have an Azure SQL database named SalesDB on a logical server named sales-sql01.

You have an Azure App Service web app named OrderApi that connects to SalesDB by using SQL authentication.

You enable a user-assigned managed identity named OrderApi-Id for OrderApi.

You need to configure OrderApi to connect to SalesDB by using Microsoft Entra authentication.

The managed identity must have read and write permissions to SalesDB.

Which Transact-SQL statements should you run in SalesDB?

A
CREATE LOGIN [OrderApi-Id] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [OrderApi-Id];
ALTER ROLE db_datawriter ADD MEMBER [OrderApi-Id];
B
CREATE USER [OrderApi-Id] WITH PASSWORD = ' P@ssw0rd! ' ;
ALTER ROLE db_datareader ADD MEMBER [OrderApi-Id];
ALTER ROLE db_datawriter ADD MEMBER [OrderApi-Id];
C
CREATE USER [OrderApi-Id] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [OrderApi-Id];
ALTER ROLE db_datawriter ADD MEMBER [OrderApi-Id];
D
CREATE LOGIN [OrderApi-Id] WITH PASSWORD = ' P@ssw0rd! ' ;
ALTER SERVER ROLE sysadmin ADD MEMBER [OrderApi-Id];

Show answer and explanation

Correct answer: C

Explanation

For an Azure App Service using a user-assigned managed identity to connect to Azure SQL Database with Microsoft Entra authentication, the required database-side step is to create a database user from the external provider, then grant the needed database roles. Microsoft's Azure SQL documentation for managed identities states that to let a managed identity access the target database, you create a SQL user for that identity by using:
CREATE USER [ < identity-name > ] FROM EXTERNAL PROVIDER;
and then assign the appropriate roles.
That makes db_datareader and db_datawriter the right role grants here, because the requirement says the identity must have read and write permissions to SalesDB.
The other options are incorrect: A uses CREATE LOGIN... FROM EXTERNAL PROVIDER, which is not the right choice for this Azure SQL Database scenario; the documented pattern is to create a database user from the external provider. B and create SQL-authentication principals with passwords, which does not meet the Microsoft Entra D managed-identity requirement.
D also grants sysadmin, which is a server-level overgrant and not appropriate for the stated read/write requirement.

Question 7 Single choice

You have a Transact-SQL procedure that builds a request body for an Azure OpenAI chat completions call
by using FOR JSON PATH.

The procedure must produce one JSON object, not an array containing one object.

Which clause should you add?

A
ROOT('request')
B
INCLUDE_NULL_VALUES
C
WITHOUT_ARRAY_WRAPPER
D
AUTO

Show answer and explanation

Correct answer: C

Explanation

Option C is correct because WITHOUT_ARRAY_WRAPPER removes the default square brackets generated by FOR JSON and returns a single JSON object when the result set contains one row. Options
A, B, and D are incorrect because ROOT changes the outer shape, INCLUDE_NULL_VALUES affects null serialization only, and AUTO changes shaping behavior but still doesn't solve the single-object requirement by itself.

Question 8 Single choice

You have an Azure SQL table that contains a column named NationalId with highly sensitive values.

You must protect the stored values and query parameters so that repeated plaintext values do not produce

repeated ciphertext values. Users must not be able to infer value patterns from the encrypted output.

Which feature should you use?

A
Always Encrypted with deterministic encryption
B
Dynamic Data Masking
C
Always Encrypted with randomized encryption
D
Row-Level Security

Show answer and explanation

Correct answer: C

Explanation

Option C is correct because randomized encryption produces different ciphertext for the same plaintext value, which prevents pattern analysis based on repeated encrypted values. Always Encrypted also keeps encryption and decryption on the client side rather than revealing plaintext to the database engine. Options
A, B, and D are incorrect because deterministic encryption always generates the same ciphertext for the same plaintext, Dynamic Data Masking is a presentation feature rather than encryption, and Row-Level Security controls row access instead of value confidentiality.