Microsoft DP-800 - Developing AI-Enabled Database Solutions

Correct Answer:

Explanation:
Verified Answer : =
* queryType # " semantic "
* semanticConfiguration # " hotels "
* captions # " extractive "
Comprehensive and Detailed Explanation with all Developing AI-Enabled Database Solutions documents : = The correct configuration is queryType: " semantic " , semanticConfiguration: " hotels " , and captions: " extractive " .
The request is already a hybrid search because it contains both a textual search value ( " ocean view " ) and a vectorQueries block targeting DescriptionVector . Microsoft documents that hybrid search executes full-text and vector searches together and merges their result sets. When semantic ranking is required, queryType must be set to " semantic " .
The semanticConfiguration value must reference the semantic configuration defined for the target index.
Since the index in this question is named hotels and the available answer choice is " hotels " , that is the appropriate configuration value in the supplied answer set. Microsoft's REST examples show that semantic queries require both queryType: " semantic " and a valid semanticConfiguration .
To return semantic captions, use " captions " : " extractive " . Microsoft states that extractive captions identify the most relevant passages from the indexed content and return them with each result. They are generated from existing document text rather than generated by an LLM.
The completed request body is therefore:
{
" search " : " ocean view " ,
" queryType " : " semantic " ,
" semanticConfiguration " : " hotels " ,
" captions " : " extractive " ,
" top " : 10,
" hybridSearch " : {
" maxTextRecallSize " : 50
},
" vectorQueries " : [
{
" kind " : " vector " ,
" vector " : [ /* embedding array */ ],
" fields " : " DescriptionVector " ,
" k " : 50
}
]
}
Final drag-and-drop selections:
* First target: " semantic "
* Second target: " hotels "
* Third target: " extractive "

Correct Answer:

Explanation:

Correct Answer:

Explanation:
* 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.

Correct Answer:

Explanation:

For the vector portion, the correct choice is VECTOR_DISTANCE and order by distance ascending . The requirement is to build a combined weighted formula using the actual vector distance. Microsoft documents that VECTOR_DISTANCE returns the exact distance between two vectors. Since lower distance means greater similarity, ascending distance is the right direction for ranking. VECTOR_SEARCH is for ANN retrieval, but this hotspot specifically asks for a weighted formula based on distance , so VECTOR_DISTANCE is the appropriate operator.
For the keyword portion, the correct choice is CONTAINSTABLE on description and return ranked matches . Microsoft documents that CONTAINSTABLE returns a RANK column from 0 through 1000 , which is exactly what is needed for weighted scoring in a hybrid formula.
For the final ranking expression, the best choice is order by (distance * 0.6) + ((1.0 - RANK/1000.0) * 0.4) .
This works because vector distance is a lower-is-better metric, while full-text RANK is a higher-is-better metric. Dividing RANK by 1000 normalizes it to the documented range, and subtracting from 1.0 converts it into a lower-is-better term so both components can be combined consistently in one ascending score. This final step is a sound inference based on Microsoft's documented distance semantics and full-text rank range.

Correct Answer:

Explanation:

The correct SARGable pattern for filtering a single calendar day is to use a half-open date range :
o.CreateDate > = @StartDate
AND o.CreateDate < @EndDate
with:
SET @EndDate = DATEADD(day, 1, @StartDate)
This is the correct design because it keeps the function off the column and applies it only to the parameter.
That allows SQL Server and Azure SQL to use an index on CreateDate efficiently, which is the key requirement for a SARGable predicate. Microsoft documents DATEADD as the standard function for adding one day to a date value, which makes it the right way to derive the exclusive upper boundary for the next day.
The incorrect choices are the ones that wrap CreateDate in CONVERT(...), because expressions like:
CONVERT(char(10), CreateDate, 121) = ...
make the predicate non-SARGable and typically prevent efficient seeks on an index over CreateDate.
So the completed procedure is:
CREATE PROCEDURE dbo.usp_SearchOrders
@StartDate date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EndDate date;
SET @EndDate = DATEADD(day, 1, @StartDate);
SELECT o.CreateDate,
o.OrderId,
o.ShipDate
FROM dbo.Orders AS o
WHERE o.CreateDate > = @StartDate
AND o.CreateDate < @EndDate;
END;