Free Microsoft DP-800 Exam Dumps August 2026

This also does not meet the goal. DROP PARTITION SCHEME removes the partition scheme object from the database; it is not the command used to remove just the rows for the oldest month from a partitioned table. Microsoft's DROP PARTITION SCHEME documentation is explicit that the statement removes the partition scheme itself.

For removing only the oldest month's rows with minimal impact, Microsoft points to partition-level maintenance operations such as truncating a single partition on a partitioned table. That targets only the needed data subset and is more efficient for retention workloads.

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(*).

For an Azure App Service using a user-assigned managed identity to connect to Azure SQL Database with Microsoft Entra authentication, the required database-side step is to create a database user from the external provider, then grant the needed database roles. Microsoft's Azure SQL documentation for managed identities states that to let a managed identity access the target database, you create a SQL user for that identity by using:

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;

and then assign the appropriate roles.

That makes db_datareader and db_datawriter the right role grants here, because the requirement says the identity must have read and write permissions to SalesDB.

The other options are incorrect:

A uses CREATE LOGIN ... FROM EXTERNAL PROVIDER, which is not the right choice for this Azure SQL Database scenario; the documented pattern is to create a database user from the external provider.

B and D create SQL-authentication principals with passwords, which does not meet the Microsoft Entra managed-identity requirement.

D also grants sysadmin, which is a server-level overgrant and not appropriate for the stated read/write requirement.

Because the SQL Server 2025 instance has no outbound network connectivity, the embedding model cannot rely on a remote REST endpoint such as Azure AI Foundry or Azure OpenAI. Microsoft's CREATE EXTERNAL MODEL documentation includes a local deployment pattern using ONNX Runtime running locally with local runtime/model paths. That is the right design when embeddings must be generated inside the SQL Server instance without external network access. Microsoft explicitly documents a local ONNX Runtime example for SQL Server 2025 and notes the required local runtime setup and model path configuration.

The permission requirement is handled by granting the application user access to use the external embeddings model. Microsoft's AI_GENERATE_EMBEDDINGS documentation states that, as a prerequisite, you must create an external model of type EMBEDDINGS that is accessible via the correct grants, roles, and/or permissions. Among the choices, the exam-appropriate action is to grant execute permission on the external model project to AlApplicationUser so only that database user can run embedding generation through the model.

Microsoft documents REGEXP_SUBSTR for Transact-SQL with the string_expression parameter as supporting character string types char, nchar, varchar, and nvarchar. For the regex functions, support for LOB types such as varchar(max) and nvarchar(max) is specifically called out for REGEXP_LIKE, REGEXP_COUNT, and REGEXP_INSTR up to 2 MB, but that support note is not listed for REGEXP_SUBSTR in the surfaced documentation. In exam terms, the safe and expected approach is to cast the nvarchar(max) column to nvarchar(4000) before calling REGEXP_SUBSTR.

This also fits the scenario detail that the ID is always contained within the first paragraph of MessageText. Since the needed value is near the start of the text, narrowing the input to a non-LOB string type such as nvarchar(4000) is sufficient and avoids incompatibility concerns with nvarchar(max).

The other options are not appropriate:

A STRING_ESCAPE(..., 'json') is for JSON escaping, not regex extraction.

C adding a case-sensitive collation changes comparison behavior, but it is not the required fix for REGEXP_SUBSTR on nvarchar(max).

D TRY_CONVERT(varchar(max), ...) still leaves a MAX type and also risks unnecessary Unicode loss.