Pass the Microsoft Certified: SQL AI Developer DP-800 Questions and answers with Dumpstech

Verified Answer : =

Vector index column → embedding

Vector search function → VECTOR_SEARCH

Final clause → ORDER BY s.distance

The first selection is embedding because CREATE VECTOR INDEX must target the column that stores vector data. Microsoft documents the syntax as CREATE VECTOR INDEX ... ON object (vector_column) , so the index belongs on dbo.Products.embedding , not on distance or product_name .

The second selection is VECTOR_SEARCH . This function is designed for vector similarity retrieval and works with vector indexes in SQL database in Microsoft Fabric . In the shown code, the supplied @query_vector VECTOR(1536) is compared against the embedding column using cosine distance, and TOP_N = 10 requests the ten nearest matches. Microsoft specifically documents VECTOR_SEARCH for approximate nearest-neighbor searches and its integration with vector indexes.

The final clause is ORDER BY s.distance because VECTOR_SEARCH returns a distance value representing how far each stored vector is from the supplied query vector. With cosine distance, smaller distance means greater similarity , so ascending order returns the most relevant products first.

Therefore, the completed selections are:

CREATE VECTOR INDEX idx_products_embedding

ON dbo.Products (embedding)

WITH (METRIC = ' cosine ' , TYPE = ' DiskANN ' );

FROM VECTOR_SEARCH (

TABLE = dbo.Products AS t,

COLUMN = embedding,

SIMILAR_TO = @query_vector,

METRIC = ' cosine ' ,

TOP_N = 10

) AS s

ORDER BY s.distance;

So the verified hotspot answers are embedding → VECTOR_SEARCH → ORDER BY s.distance .

To speed up repeated aggregate retrieval without changing base-table structure, the right pattern is an indexed view . Microsoft requires that an indexed view be created with WITH SCHEMABINDING , and if the view uses GROUP BY , it must also include COUNT_BIG(*) . After that, the first index on the view must be a unique clustered index .

The other options fail Microsoft’s indexed-view rules:

A is invalid because ORDER BY is not allowed in the indexed-view definition.

B is invalid because indexed views require WITH SCHEMABINDING , and the first index cannot just be a nonclustered index.

C is incomplete because a grouped indexed view must include COUNT_BIG(*) .

The best explanation is an open explicit transaction . During the incident, session 72 was sleeping but still had open_transaction_count = 1 , and sys.dm_exec_input_buffer(72, NULL) showed only BEGIN TRANSACTION UPDATE Sales.Orders. That pattern indicates the session executed an update inside an explicit transaction and then remained idle without committing or rolling back , while still holding locks. Other sessions showing blocking_session_id = 72 is the expected symptom of that situation. Microsoft explains that blocking occurs when one session holds a lock on a resource and another session requests a conflicting lock, and sleeping sessions can continue to block if they retain locks through an open transaction.

This also fits the observed behavior that the timeouts stopped only after session 72 was terminated . Killing the session would roll back the active transaction and release the locks, allowing waiting updates to continue. That is much more consistent with an uncommitted transaction than with a deadlock, because deadlocks are normally detected and one session is chosen as the victim automatically rather than persisting until manual termination.

The correct way to avoid full table scans here is to add a computed column that extracts the JSON scalar property with JSON_VALUE , and then create a nonclustered index on that computed column with the report’s returned columns in the INCLUDE list. Microsoft’s JSON indexing guidance specifically recommends creating a computed column that exposes the JSON property you filter on, using the same expression as in the query, and then indexing that computed column.

So the computed column must be:

AS JSON_VALUE([log], ' $.severity ' ) PERSISTED

This is correct because $.severity is a scalar JSON value, so JSON_VALUE is the proper function. JSON_QUERY would be for extracting an object or array, not a scalar property. Microsoft also notes that persisted computed columns can improve access speed for JSON-derived values.

The index should then include:

INCLUDE (LogId, LogDateTime, [log])

That is the right covering strategy because the report filters by severity but returns LogId, LogDateTime, and log. Microsoft’s guidance on included columns explains that nonkey included columns let a nonclustered index cover more queries and reduce extra lookups to the base table.

So the completed code is:

ALTER TABLE WebSite.Logs

ADD severity AS JSON_VALUE([log], ' $.severity ' ) PERSISTED;

GO

CREATE INDEX ix_severity

ON WebSite.Logs(severity)

INCLUDE (LogId, LogDateTime, [log]);

GO

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

The requirement is very specific: prevent ddl_Audit from firing during the next deployment , but leave the trigger object in place . Microsoft documents DISABLE TRIGGER as the statement used to disable a trigger without dropping it. That is exactly the right operation for a temporary suspension of a DDL trigger. For a database-scoped DDL trigger, the syntax is on the database scope, for example DISABLE TRIGGER ddl_Audit ON DATABASE;.

The other options do not meet the requirement as directly:

ALTER TRIGGER changes the trigger definition, not simply disables execution.

ALTER DATABASE is not the direct statement for disabling a specific DDL trigger.

ALTER SERVER AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION are audit-feature statements, not trigger-control statements.

So the correct Transact-SQL statement is DISABLE TRIGGER .

where exists 2. where p.PatientID = pr.PatientID Otherwise we filter PatientID twice. Having in the subquery does not work without group by and the date filter does not work without where.

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.

ON → m.maintenanceId = i.maintenanceId

WHERE → m.LastModifiedUtc < > i.LastModifiedUtc

The correct drag-and-drop completion is:

ON m.maintenanceId = i.maintenanceId

WHERE m.LastModifiedUtc < > i.LastModifiedUtc

This satisfies the requirement to ensure that when a row in MaintenanceEvents changes, the corresponding LastModifiedUtc value is updated to the current system time, while also helping avoid unnecessary repeat updates.

The inserted pseudo-table in a SQL Server AFTER UPDATE trigger contains the rows that were just updated. To update the matching row in the base table correctly, the trigger must join the target table row to the corresponding row in inserted by the table’s primary key. In this schema, MaintenanceId is the primary key for MaintenanceEvents, so the correct join is m.maintenanceId = i.maintenanceId . Joining on VehicleId would be incorrect because multiple maintenance rows could exist for the same vehicle, which could update unintended rows. Microsoft’s trigger documentation explains that inserted and deleted are used to work with the affected rows and that multi-row logic should be based on proper key matching.

The WHERE m.LastModifiedUtc < > i.LastModifiedUtc predicate is used to prevent the trigger from re-updating rows where the timestamp already matches the value in inserted. That reduces redundant writes and supports the requirement to avoid recursive or repeated update behavior. In practice, this means the trigger updates only rows whose current stored timestamp differs from the just-updated version. This is the exam-appropriate pattern for a self-updating timestamp column in an AFTER UPDATE trigger.