Master Microsoft DP-800: AI-Enabled Database Solutions Practice

Correct : D

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());

Correct : C

The correct answer is C because the email must be normalized by removing only the subaddressing portion between the plus sign and the @, while preserving the domain. JSON_VALUE is the correct function to extract the scalar email value from the JSON document. Microsoft states that JSON_VALUE is used to extract a scalar value from JSON text.

Then REGEXP_REPLACE should remove the pattern \+.*@ and replace it with a single @. For example:

user1+promo@contoso.com user1@contoso.com

Microsoft documents that REGEXP_REPLACE returns the source string with text matching the regular expression replaced by the replacement string, and that an empty or custom replacement string can be used to reshape the result.

Why the other options are wrong:

A removes everything from + to the end of the string, which would leave user1 and lose @contoso.com.

B tries to extract a string that already excludes +..., but it does not reliably reconstruct the normalized address in this pattern.

D also removes everything after +, including the domain, which is incorrect.

So the normalized-email expression is:

REGEXP_REPLACE(JSON_VALUE(Payload, '$.customer_email'), '\+.*@', '@')

Correct : B

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.

Correct : B

For an SDK-style SQL database project targeting Azure SQL Database, Microsoft recommends using the Azure SQL system DACPAC as a NuGet package reference rather than adding a direct artifact reference to master.dacpac for new SDK-style development. Microsoft's SQL Database Projects documentation says direct .dacpac artifact references are not recommended for new development in SDK-style projects; instead, use NuGet package references.

Because the goal is specifically to make dotnet build validate successfully with the correct Azure SQL system objects, adding an artifact reference to master.dacpac is not the recommended SDK-style solution. It can work in some project styles, but it does not meet the stated goal as the proper approach for SDK-style Azure SQL projects.

Correct : B

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.