Microsoft DP-800 Exam With Confidence Using Practice Dumps

The first statement is No . The requirement says telemetry data must be stored in a partitioned table to provide predictable performance for ingestion and retention operations. However, the shown CREATE TABLE statement does not define a partition function or partition scheme, and the table is created with a regular clustered primary key on TelemetryId. Microsoft’s partitioning guidance states that creating a partitioned table requires a partition function , a partition scheme , and creating the table or index on that partition scheme using a partitioning column. None of that appears in the code, so the table is not partitioned.

The second statement is Yes . The code creates a JSON index named JI_VehicleTelemetry_Location on LocationJson for these specific JSON paths: $.location.latitude, $.location.longitude, and $.location.accuracy. That matches the requirement that those JSON properties must be filterable by using an index seek . Microsoft documents that JSON indexing is used to optimize filtering and sorting on JSON properties, and the index only helps for the properties included in the index definition.

The third statement is No . The JSON index is defined only for latitude, longitude, and accuracy. A query filtering on $.location.heading references a different path that is not included in the index definition, so that query would not use JI_VehicleTelemetry_Location for that predicate. JSON indexes are path-specific; they do not automatically cover unrelated properties in the same JSON document.

The correct answer is D because the requirement is to enable similarity search over embedding vectors and to minimize latency . Microsoft documents that CREATE VECTOR INDEX is specifically used to create an index on vector data for approximate nearest neighbor (ANN) search , which is designed to accelerate vector similarity queries compared to exact k-nearest-neighbor scans.

This matches the scenario exactly. The VehicleHealthSummary table already includes an Embeddings (vector(1536)) column. In Microsoft SQL platforms, embeddings are stored in vector columns and queried for semantic similarity. To improve performance and reduce response time, Microsoft recommends a vector index , not a regular B-tree nonclustered index and not a full-text index. A vector index is purpose-built for finding the most similar vectors efficiently.

The other options are not appropriate:

A would require manual comparison logic and would increase latency rather than minimize it.

B is incorrect because a standard nonclustered index is not the index type used for vector similarity operations.

C is incorrect because full-text indexes are for textual token-based search, not numeric vector embeddings.

Microsoft’s current documentation is explicit that vector indexes support approximate nearest neighbor search , and that the optimizer can use the ANN index automatically for vector queries. That is the exam-aligned design choice when the goal is fast retrieval of the most relevant health summary reports from an embeddings column.

PhoneRaw extraction → JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

PhoneNumerals → REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

WHERE condition → p.PhoneRaw

The first correct selection is JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) because the phone number is stored as a scalar JSON property . Microsoft documents that JSON_VALUE extracts a scalar value from JSON text, whereas JSON_QUERY is intended for JSON objects or arrays.

The second selection is REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) . The regular expression [^0-9] matches every character that is not a digit . Replacing all such characters with an empty string converts a formatted value such as:

+000 000-000-0000

into:

0000000000000

Microsoft documents that REGEXP_REPLACE returns the source string after occurrences matching the regular expression are replaced, and it applies to Azure SQL Database.

The final blank is p.PhoneRaw , producing:

WHERE p.PhoneRaw IS NOT NULL;

This excludes customers that do not have a phone value while preserving one output row for each customer that does.

The completed query is:

WITH PhoneCTE AS

(

SELECT DISTINCT

c.CustomerId,

JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) AS PhoneRaw

FROM dbo.Customers AS c

)

SELECT

p.CustomerId,

REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) AS PhoneNumerals

FROM PhoneCTE AS p

WHERE p.PhoneRaw IS NOT NULL;

Final drag-and-drop selections:

First target: JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

Second target: REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

Third target: p.PhoneRaw