Microsoft DP-800 Dumps PDF Free Download - Page: 9

QUESTION: 9 Exam Topic: Contoso Case Study

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;

· Al workloads

· Vector search

· Modernized API access

· 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:

· 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.

Contoso identifies the following maintenance data requirements:

· 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.

· 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:

· 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.
Hide Case Study

HOTSPOT

You need to create a table in the database to store the telemetry data. You have the following Transact-SQL code.

See Explanation for the Answer.

Answer(s): A

Explanation:

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.

, Misc. Questions Types

Reveal Solution Next Question

QUESTION: 10 Exam Topic: Contoso Case Study

Show Case Study

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

DRAG DROP (Drag and Drop is not supported)

You have an Azure SQL database named SalesDB that contains tables named Sales.Orders and Sales.OrderLines. Both tables contain sales data

You have a Retrieval Augmented Generation (RAG) service that queries SalesDB to retrieve order details and passes the results to a large language model (ILM) as JSON text. The following is a sample of the JSON.

You need to return one 1SON document per order that includes the order header fields and an array of related order lines. The LIM must receive a single JSON array of orders, where each order contains a lines property that is a JSON array of line Items.

Which transact-SQL commands should you use to produce the required JSON shape from the relational tables? To answer, drag the appropriate commands to the correct operations. Each command may be used once, more than once, or not at all. Vou may need to drag the split bar between panes or scroll to view content.

Note: Each correct selection is worth one point.

See Explanation for the Answer.

Answer(s): A

Explanation:

Serialize the order-level JSON: FOR JSON PATH

Generate a nested lines array: JSON_QUERY

Extract a single scalar value from the JSON text: JSON_VALUE

The correct mapping is based on how SQL Server and Azure SQL JSON functions are designed to shape relational data into JSON for AI and RAG scenarios.

To serialize the order-level JSON, use FOR JSON PATH. Microsoft documents that FOR JSON PATH gives you full control over the JSON output shape and formats the result as an array of JSON objects. It is the standard way to turn relational query results into the JSON structure needed by downstream consumers such as APIs and LLM-based RAG services. It also supports nested output through subqueries and aliases.

To generate a nested lines array, use JSON_QUERY. Microsoft explains that JSON_QUERY returns a JSON object or array from JSON text, and it is used when you want to preserve a JSON fragment instead of treating it as plain text. In this scenario, the nested lines property must be emitted as a proper JSON array inside each order document, so JSON_QUERY is the correct command to embed that array in the final JSON shape.

To extract a single scalar value from the JSON text, use JSON_VALUE. Microsoft explicitly states that JSON_VALUE extracts a scalar value from a JSON string, while JSON_QUERY is for objects or arrays. So whenever the requirement is to pull out one property such as an order number, currency code, or customer ID from JSON text, JSON_VALUE is the correct function.

The unused commands are not the best fit here:

OPENJSON is primarily for parsing JSON into rows and columns, not for shaping relational tables into nested output.

JSON_MODIFY is for updating JSON text, not generating the required output structure.

So the drag-and-drop answers are:

Serialize the order-level JSON FOR JSON PATH

Generate a nested lines array JSON_QUERY

Extract a single scalar value from the JSON text JSON_VALUE

Reveal Solution Next Question

QUESTION: 11 Exam Topic: Contoso Case Study

Show Case Study

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

You have an Azure SQL table that contains the following data.

You need to retrieve data to be used as context for a large language model (LLM). The solution must minimize token usage.

Which formal should you use to send the data to the LLM?

A)

B)

C)

D)

Option A
Option B
Option C
Option D

Answer(s): A

Explanation:

The correct choice is Option A because it provides the relevant semantic context the LLM needs while avoiding an unnecessary field that would add tokens without improving answer quality.

For LLM grounding and RAG-style context, Microsoft guidance emphasizes mapping and sending the fields that contain text pertinent to the use case. In this FAQ scenario, the useful context is the ProductName, the Question, and the Answer. Those three fields help the model understand both the subject domain and the actual Q&A pair. By contrast, FaqId is just a technical identifier and generally adds no semantic value for response generation, so including it wastes tokens.

That is why Option A is better than the others:

Option A keeps the meaningful text fields and removes the low-value identifier.

Option B is too minimal because it includes only the answer text as Prompt, which strips away the product and question context the LLM may need for accurate grounding.

Option C keeps FaqId but omits ProductName, which can be important disambiguating context.

Option D includes everything, but that does not minimize token usage because it keeps the unnecessary FaqId.

Reveal Solution Next Question

QUESTION: 12 Exam Topic: Contoso Case Study

Show Case Study

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

You have a Microsoft SQL Server 2025 instance that has a managed identity enabled.

You have a database that contains a table named dbo.ManualChunks. dbo.ManualChunks contains product manuals.

A retrieval query already returns the top five matching chunks as nvarchar(max) text.

You need to call an Azure OpenAI REST endpoint for chat completions. The solution must provide the highest level of security.

You write the following Transact-SG1 code.

What should you insert at line 02?

A)

B)

C)

D)

E)

Option A
Option B
Option C
Option D
Option E

Answer(s): B

Explanation:

The correct answer is Option B because the requirement is to call an Azure OpenAI REST endpoint from SQL Server 2025 while providing the highest level of security, and the instance already has a managed identity enabled. For Microsoft's SQL AI features, the preferred secure pattern is to use a database scoped credential with IDENTITY = 'Managed Identity' instead of storing an API key. Microsoft documents that SQL Server 2025 supports managed identity for external AI endpoints, and

for Azure OpenAI the credential secret uses the Cognitive Services resource identifier:
{"resourceid":"https://cognitiveservices.azure.com"}.

So line 02 should be:

WITH IDENTITY = 'Managed Identity',
SECRET = '{"resourceid":"https://cognitiveservices.azure.com"}';

Why the other options are incorrect:

A and D use HTTP header or query-string credentials with an API key, which is less secure than

managed identity because a secret key must be stored and rotated manually. Microsoft recommends managed identity where supported to avoid embedded secrets.

C mixes Managed Identity with an api-key secret, which is not the correct pattern for Azure OpenAI managed-identity authentication.

E uses an invalid identity value for this scenario. The accepted credential identities for external REST endpoint calls include HTTPEndpointHeaders, HTTPEndpointQueryString, Managed Identity, and Shared Access Signature.

Because the endpoint is Azure OpenAI and the question explicitly asks for the highest security, managed identity with the Cognitive Services resource ID is the Microsoft-aligned answer.

Reveal Solution Next Question

QUESTION: 13 Exam Topic: Contoso Case Study

Show Case Study

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

HOTSPOT

You have an Azure SQL database that contains the following tables and columns.

Embeddings in the NotesEnbeddings and DescriptionEabeddings tables have been generated from values in the Description and notes columns of the Articles table by using different chunk sizes.

You need to perform approximate nearest neighbor (ANN) queries across both embedding tables. The solution must minimize the impact of using different chunk sizes.

What should you use? To answer, select the appropriate options in the answer area.

Note: Each correct selection is worth one point.

See Explanation for the Answer.

Answer(s): A

Explanation:

The correct function is VECTOR_SEARCH because the requirement is to perform approximate nearest neighbor (ANN) queries. Microsoft's SQL documentation states that VECTOR_SEARCH is the function used for vector similarity search, and that an ANN index is used only with VECTOR_SEARCH when a compatible vector index exists on the target column. By contrast, VECTOR_DISTANCE calculates an exact distance and does not use a vector index for ANN retrieval.

The correct distance metric is cosine distance. Microsoft documents that VECTOR_SEARCH supports cosine, dot, and euclidean metrics, and Microsoft guidance specifically notes that cosine similarity is commonly used for text embeddings. It also states that retrieval of the most similar texts to a given text typically functions better with cosine similarity, and that Azure OpenAI embeddings rely on

cosine similarity to compute similarity between a query and documents. Since both NotesEmbeddings and DescriptionEmbeddings are text-derived embeddings and the goal is to minimize the impact of different chunk sizes, cosine is the best choice because it compares direction/angle rather than being as sensitive to vector magnitude as Euclidean distance.

Reveal Solution Next Question

QUESTION: 14 Exam Topic: Contoso Case Study

Show Case Study

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

Vou have an Azure SQL database named SalesDB that contains a table named dbo. Articles, dbo.Articles contains two million articles with embeddmgs. The articles are updated frequently throughout the day.

You query the embeddings by using VECTOR_SEARQi

Users report that semantic search results do NOT reflect the updates until the following day.

Vou need to ensure that the embeddings are updated whenever the articles change. The solution must minimize CPU usage on SalesDB

Which embedding maintenance method should you implement?

Modify the query to use VECTOR.DTSTANCF instead of VECTOR.SEARCK
enable change data capture (COC) on dbo.Articles and use an Azure Functions app to process CLX changes.
Run an hourly Transact-SQL job that regenerates embeddings for all the rows in dbo.Articles.
On dbo.Articles, create a trigger that calls AI GENERATE EMBEDOINGS for each inserted or updated row.

Answer(s): B

Explanation:

The correct answer is B because the problem is not the vector search operator itself. The problem is that embeddings are becoming stale when article content changes. Microsoft documents that change data capture (CDC) tracks insert, update, and delete operations on source tables, which makes it the right mechanism to identify only the rows that changed.

This also best satisfies the requirement to minimize CPU usage on SalesDB. With CDC, the database only records the row changes, and the embedding regeneration work can be moved to an external process such as an Azure Functions app. That avoids running embedding generation inline inside the database for every update and avoids repeatedly recalculating embeddings for unchanged rows. In contrast, an hourly full-table regeneration would be extremely wasteful on a table with two million frequently updated articles, and a trigger that calls embedding generation per row would push expensive AI work into the transactional path of the database.

Option A is incorrect because changing from VECTOR_SEARCH to VECTOR_DISTANCE does not regenerate embeddings; it only changes the retrieval method. Microsoft states that VECTOR_SEARCH is the ANN search function, while VECTOR_DISTANCE performs exact distance calculation, so neither option addresses stale embedding data.

So the right design is:

use CDC to detect only changed articles,

process those changes outside the database,

regenerate embeddings only for changed rows,

write back the refreshed embeddings for current semantic search results.

Reveal Solution Next Question

QUESTION: 15 Exam Topic: Contoso Case Study

Show Case Study

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

HOTSPOT

You have a SQL database in Microsoft Fabric named Sales BD that contains a table named dbo.Products. You need to modify SalesBD to meet the following requirements:

· Create a vector index on the appropriate column.

· Use a supplied natural language query vector.

How should you complete the Transact-SQL code? To answer, select the appropriate options in the answer area.

Note: Each correct selection is worth one point.

See Explanation for the Answer.

Answer(s): A

Explanation:

The first correct selection is embedding because a vector index must be created on the vector column, not on a scalar distance column or a text column such as product_name. Microsoft's CREATE VECTOR INDEX documentation shows that the index is created directly on the vector-valued column, for example ON product_embeddings(embedding).

The second correct selection is VECTOR_SEARCH because the requirement is to use a supplied natural language query vector and search against the indexed embeddings. Microsoft documents that VECTOR_SEARCH is the Transact-SQL function for approximate nearest neighbor vector retrieval and that it applies to SQL database in Microsoft Fabric as well as other supported SQL platforms.

This also matches the shown code pattern:

declare a vector variable such as @query_vector VECTOR(1536),

create a vector index on dbo.Products(embedding),

query with VECTOR_SEARCH(... SIMILAR_TO = @query_vector, METRIC = 'cosine', TOP_N = 10).

Reveal Solution Next Question

QUESTION: 16 Exam Topic: Contoso Case Study

Show Case Study

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

You have an Azure SQL database That contains a table named dbo.Products, dbo.Products contains three columns named Embedding Category, and Price. The Embedding column is defined as VECTOR(1536).

You use Ai_GENERME_EMBEDOINGS and VECTOR_SEARCH to support semantic search and apply additional filters on two columns named Category and Price.

You plan to change the embedding model from text-embedding-ada-002 to text-embedding-3-smalL

Existing rows already contain embeddings in the Embedding column.

You need to implement the model change. Applications must be able to use VECTOR_SEARCH without runtime errors.

What should you do first?

Regenerate embeddings for the existing rows.
Normalize the vector lengths before storing new embeddings.
Convert the Embedding column to nvacchar(mex).
Create a vector index on dbo.Products.Embedding.

Answer(s): A

Explanation:

When you change embedding models, the stored vectors should be treated as belonging to a different embedding space unless you intentionally keep the entire corpus consistent. Microsoft's vector guidance notes that when most or all embeddings are replaced with fresh embeddings from a new model, the recommended practice is to reload the new embeddings and, for large-scale replacement scenarios, consider dropping and recreating the vector index afterward so search quality remains predictable.

This question also says applications must continue to use VECTOR_SEARCH without runtime errors. VECTOR_SEARCH requires compatible vector dimensions, and the vector column already exists. Azure OpenAI documentation shows that text-embedding-ada-002 is fixed at 1536 dimensions and text-embedding-3-small supports up to 1536 dimensions. That means the migration can remain compatible with a VECTOR(1536) column, but the right implementation step is still to re-embed the existing rows so the table does not contain a mixed corpus produced by different models.

The other options are not appropriate:

B normalization does not solve a model migration problem.

C converting the vector column to nvarchar(max) would break vector-native search design.

D a vector index improves performance, but it does not migrate old embeddings to the new model.

Reveal Solution Next Question

Microsoft Developing AI-Enabled Database Solutions DP-800 Exam Questions in PDF

Free Microsoft DP-800 Dumps Questions (page: 9)

QUESTION: 9 Exam Topic: Contoso Case Study

Explanation:

QUESTION: 10 Exam Topic: Contoso Case Study

Explanation:

QUESTION: 11 Exam Topic: Contoso Case Study

Explanation:

QUESTION: 12 Exam Topic: Contoso Case Study

Explanation:

QUESTION: 13 Exam Topic: Contoso Case Study

Explanation:

QUESTION: 14 Exam Topic: Contoso Case Study

Explanation:

QUESTION: 15 Exam Topic: Contoso Case Study

Explanation:

QUESTION: 16 Exam Topic: Contoso Case Study

Explanation: