DP-800 Developing AI-Enabled Database Solutions Questions and Answers

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.

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.

JSON_VALUE(f.FeedbackJson, ' $.text ' ) AS FeedbackText

CONTAINS(FeedbackJson, @Keyword)

SimilarityScore

These three selections are the correct way to complete the query because they align exactly with the stated requirements for the FeedbackJson column.

First, to extract the customer feedback text from the JSON document , the correct expression is JSON_VALUE(f.FeedbackJson, ' $.text ' ) AS FeedbackText . Microsoft documents that JSON_VALUE is used to extract a scalar value from JSON, while JSON_QUERY is used for returning an object or array . Since $.text is the textual feedback string, JSON_VALUE is the correct function.

Second, to filter rows where the JSON text contains a keyword , the best choice is CONTAINS(FeedbackJson, @Keyword) . The scenario explicitly states that FeedbackJson already has a full-text index , and Microsoft documents that CONTAINS is the full-text predicate used in the WHERE clause to search full-text indexed character data. That makes it more appropriate than using EDIT_DISTANCE for keyword filtering.

Third, to order the results by similarity score, highest first , the correct item is SimilarityScore in the ORDER BY clause, which would be paired with DESC in the query. This matches the requirement to sort by the computed fuzzy similarity value. The DP-800 study guide specifically includes writing queries that use fuzzy string matching functions such as EDIT_DISTANCE, which supports the earlier computed SimilarityScore expression in the query.

The correct column to use for generating embeddings is incidentDescrlption because embeddings are intended to represent the semantic meaning of rich textual content , not simple categorical, numeric, or location-only values. Microsoft’s DP-800 study guide explicitly includes skills such as identifying which columns to include in embeddings , generating embeddings , and implementing semantic vector search for scenarios where users need to find similar records based on meaning rather than exact matches.

In this scenario, analysts report that it is difficult to find similar incidents based on details such as weather, traffic conditions, and location . Those are descriptive context elements that are typically captured in a free-text incident description field. An embedding generated from incidentDescrlption can encode the semantic relationships among these narrative details, making it suitable for similarity search , semantic search , and RAG retrieval . Microsoft documentation on vectors and embeddings explains that embeddings are generated from text data and then stored for vector search to find semantically related items.

The other options are weaker choices:

vehicleLocation is too narrow and usually better handled with geospatial filtering , not embeddings.

incidentType is likely categorical and too low in semantic richness.

SeverityScore is numeric and not appropriate as the primary source for semantic embeddings.

Microsoft also notes that when multiple useful attributes exist, you can either embed each text column separately or concatenate relevant text fields into one textual representation before generating the embedding. But among the options given, the best and most exam-aligned answer is the textual narrative column : incidentDescrlption .

The correct recommendation is to retrieve grounding data from knowledge_base and, at inference time, generate query embeddings and run a vector similarity search .

The chatbot currently answers some general HR questions but fails when policies change and when users ask about internal policy documents by title or category . That is exactly the kind of problem RAG is meant to solve: ground the LLM in the organization’s proprietary content instead of relying on the model’s training data or unrelated transactional tables. Microsoft’s RAG guidance states that RAG extends LLMs by grounding responses in your own content and that, for agentic retrieval, knowledge bases unify knowledge sources for retrieval.

So the grounding data should come from knowledge_base , because that table stores the HR policy documents and already includes fields like title, content, category, and embedding. Those are the fields directly tied to the missing and outdated policy answers. By contrast:

employee_profiles and benefits_enrollment are operational HR tables, not the authoritative store for policy-document grounding.

PDF exports of the policies would be inferior to querying the indexed/structured knowledge base already prepared for retrieval.

The LLM training data is specifically the wrong source when the issue is outdated internal content.

For the retrieval step, Microsoft’s guidance says to use embeddings for vector queries and notes that vector similarity search matches concepts, not exact terms . This is especially important because users ask about policy documents by title or category and also phrase questions in ways that might not exactly match document wording. Generating a query embedding and then running a vector similarity search is the appropriate retrieval step in a RAG pipeline.

The correct answer is D because the scalar UDF must return the number of whole years from the input @OrderDate to the current date/time as a single positive integer . The correct DATEDIFF order is:

DATEDIFF(year, @OrderDate, GETDATE())

Microsoft documents that DATEDIFF(datepart, startdate, enddate) returns the count of specified datepart boundaries crossed between the start and end values. Since @OrderDate is the earlier date and GETDATE() is the later date, this ordering returns a positive result for past order dates.

The other choices are incorrect:

A reverses the arguments and would return a negative value for a past order date.

B is missing RETURN, and converting month difference to years by dividing by 12 is not the direct whole-year expression the question asks for.

C subtracts year parts only, which can be off around anniversary boundaries because it ignores whether the full year has actually elapsed.

So the correct insertion at line 05 is:

RETURN DATEDIFF(year, @OrderDate, GETDATE());

The correct answer is D because the policy explicitly prohibits sharing customer PII, secrets, and query result sets with any AI service. The safe way to use GitHub Copilot Chat here is to provide only schema-level information such as table names, column names, relationships, and the required procedure behavior, without sharing actual table contents or result sets. That lets Copilot help generate and review the Transact-SQL while avoiding disclosure of customer data. This is consistent with Microsoft and GitHub guidance that content provided in prompts is what the AI can use, so avoiding real data in the prompt is the appropriate control.

The other options violate the requirement:

A pastes real rows containing email addresses, which is direct PII disclosure.

B shares actual query result sets, which the policy forbids.

C provides the connection string so Copilot can validate against the database, which is inappropriate because it exposes connection details and could enable access beyond schema-only assistance.

So the correct approach is to ask Copilot to generate the stored procedure using only the schema and requirements, not real customer data.

The table definition allows Owner to be nullable because it was created as Owner nvarchar(100) without NOT NULL. Since the question asks what to add so that future rows must have a value, a check constraint such as CHECK (Owner IS NOT NULL) is the appropriate choice. Microsoft documents that check constraints validate future INSERT and UPDATE operations against the constraint condition.

The other options do not solve the requirement:

A foreign key enforces referential integrity, not non-null entry by itself.

A nonclustered index does not require values to be present.

A unique constraint prevents duplicate values but still does not serve as the right mechanism here for enforcing presence across future writes. Microsoft’s constraint documentation also notes that primary-key columns are implicitly NOT NULL, which helps distinguish nullability enforcement from other constraint types.

This does meet the goal. Microsoft documents that SDK-style SQL projects can add the master.dacpac database reference as a package reference , and for Azure SQL Database the correct package is the Azure-specific master DACPAC package. The Azure SQL system DACPACs are available through NuGet, and this is the recommended way to include the right system objects in the database model for dotnet build validation.

So for an SDK-style SQL database project that targets Azure SQL Database, adding Microsoft.SqlServer.Dacpacs.Azure.Master is the correct fix for unresolved references to system objects.

The requirement says the stored values and query inputs must both be protected, and users must not be able to infer relationships or repetitions in the data from the encrypted output. Microsoft documents that deterministic encryption always produces the same ciphertext for the same plaintext , which allows equality comparisons but also leaks patterns. By contrast, randomized encryption produces a different encrypted value each time for the same plaintext, which improves security and prevents pattern analysis based on repeated ciphertext values.

That makes randomized encryption the right choice here:

It protects data at rest and in transit/query parameters under Always Encrypted’s client-side encryption model.

It prevents attackers from learning that the same plaintext value appears repeatedly, because repeated inputs do not produce repeated ciphertext.

Why the other options are wrong:

A. Always Encrypted with secure enclaves adds richer confidential query support, but the key protection property the question is testing is the encryption type. The requirement to prevent inference from repeated ciphertext points specifically to randomized encryption .

C. RLS controls row access, not value confidentiality.

D. Deterministic encryption allows equality-based operations but leaks repetition patterns, which the question explicitly forbids.

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.

The correct sequence is:

git fetch origin

git merge origin/main

gh pr create

This is the right workflow because the script starts on the local feature branch:

git checkout feature/db1-add-staticdata

To update that local feature branch with the latest changes from main, you first fetch the latest remote refs with git fetch origin , then merge the updated remote main branch into the current feature branch with git merge origin/main . After the feature branch is up to date, the correct GitHub CLI command to open the pull request is gh pr create . GitHub’s CLI documentation shows that gh pr create is the command used to create a pull request, and supports flags such as --title, --body, --head, --base, --repo, and --web, which match the script shown in the question.

The other commands are not the best fit here:

git checkout main would move you off the feature branch, which is not what you want before merging main into the feature branch.

git pull origin main could update from remote main, but the script pattern here clearly separates fetching and then merging.

gh pr merge merges an existing pull request, not create one.

gh pr ready marks a draft PR as ready for review, but does not create the PR.

The scenario says Query Store already shows two persisted execution plans for the same query, and the older plan performed much better than the newer one during the last hour. Microsoft documents that sp_query_store_force_plan is used to force a particular plan for a particular query in Query Store . That makes it the fastest way to restore the previously good plan without changing application code , which is exactly what the question requires.

Why the other options are not the best fit:

sp_query_store_set_hints is for adding or updating Query Store hints to influence compilation behavior, but when you already know the exact older good plan, Microsoft points to plan forcing as the direct remedy.

DBCC FREEPROCCACHE clears cached plans broadly and is disruptive; it does not guarantee a return to the known good plan.

ALTER DATABASE is too general and does not directly restore the prior execution plan.

So the right Transact-SQL command is:

EXEC sp_query_store_force_plan

using the relevant @query_id and @plan_id from Query Store for the older, better-performing plan. Microsoft also notes that when a plan is forced, SQL Server tries to use that plan whenever it encounters the query again.