DP-800 Questions and Answers

After the INSERT → SET @OrderId = SCOPE_IDENTITY()

Inside CATCH → IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

The correct drag-and-drop choices are:

SET @OrderId = SCOPE_IDENTITY()

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

After the INSERT, the procedure should assign the newly generated identity value to the output parameter by using SCOPE_IDENTITY() . Microsoft documents that SCOPE_IDENTITY() returns the last identity value inserted in the same scope, which makes it the correct choice for returning the new OrderId from the procedure.

Inside the CATCH block, the procedure should use IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION before THROW. This ensures any open transaction is rolled back only when one actually exists, which prevents transaction-state issues and guarantees the failure aborts the transaction cleanly. Keeping THROW after the rollback is also the correct modern pattern because THROW re-raises the error to the caller with the original error information intact, giving consistent error behavior. This matches SQL Server best practice for TRY...CATCH transaction handling.

The correct function is VECTOR_SEARCH because the requirement is to perform approximate nearest neighbor (ANN) queries. Microsoft’s SQL documentation states that VECTOR_SEARCH is the function used for vector similarity search, and that an ANN index is used only with VECTOR_SEARCH when a compatible vector index exists on the target column. By contrast, VECTOR_DISTANCE calculates an exact distance and does not use a vector index for ANN retrieval.

The correct distance metric is cosine distance. Microsoft documents that VECTOR_SEARCH supports cosine, dot, and euclidean metrics, and Microsoft guidance specifically notes that cosine similarity is commonly used for text embeddings. It also states that retrieval of the most similar texts to a given text typically functions better with cosine similarity, and that Azure OpenAI embeddings rely on cosine similarity to compute similarity between a query and documents. Since both NotesEmbeddings and DescriptionEmbeddings are text-derived embeddings and the goal is to minimize the impact of different chunk sizes, cosine is the best choice because it compares direction/angle rather than being as sensitive to vector magnitude as Euclidean distance.

Verified Answer : =

Support agents must see only the rows of their own TenantId → Create a row-level security (RLS) predicate that uses the user context.

Support agents must see only the domain name portion of CustomerEmail → Configure dynamic data masking on selected columns.

Comprehensive and Detailed Explanation with all Developing AI-Enabled Database Solutions documents : =

For the TenantId requirement, use Row-Level Security (RLS) . RLS is designed to restrict which rows a database principal can access. A security predicate can compare the row ' s TenantId with tenant information derived from the current user ' s session or security context, so support agents querying the same Tickets table see only rows belonging to their own tenant. This is the correct multitenant isolation mechanism because it is enforced by the database rather than relying on individual reporting tools or application-side filtering.

For CustomerEmail , use Dynamic Data Masking (DDM) . DDM controls how sensitive column values are displayed to users who do not have UNMASK permission. A partial masking rule can conceal the local/user- specific portion of an email address while exposing only the required portion, such as its domain, according to the masking configuration.

The remaining choices do not satisfy these specific requirements. Transparent Data Encryption (TDE) protects database files at rest but does not filter rows or selectively obscure query results. Always Encrypted protects sensitive values cryptographically and is appropriate when the database engine itself should not see plaintext, but it is not the right mechanism when support agents must deliberately see a controlled portion of an email address.

Final drag-and-drop selections:

Own TenantId rows → Create a row-level security (RLS) predicate that uses the user context

Only domain portion of CustomerEmail → Configure dynamic data masking on selected columns

For authentication , the correct choice is Enable a managed identity and use Microsoft Entra authentication . Azure App Service supports managed identity, and Microsoft documents this as the recommended way to connect to Azure SQL Database without passwords or secrets . This minimizes administrative effort because there are no SQL passwords to rotate, store, or manage.

For network access , the correct choice is Create a private endpoint and disable public network access . Microsoft documents that when public network access is disabled , Azure SQL Database only accepts connections through private endpoints . That keeps the database traffic on Azure private networking rather than exposing it through the public endpoint. Microsoft also notes that simply adding a private endpoint does not disable public access by itself, so disabling public network access is part of the correct configuration.

The other options are weaker:

Rotated SQL credentials or storing SQL logins in Key Vault still use passwords and create more admin overhead.

Allowing Azure services, adding outbound IP firewall rules, or database firewall rules still rely on the public endpoint , so they do not best satisfy the requirement that database traffic remain within the subscription.

The blocking is caused by an uncommitted explicit transaction in the blocking session that is holding locks. → Yes

While an UPDATE operation on Sales.Orders is occurring, SELECT statements will be blocked. → No

Joining sys.dm_tran_locks to sys.dm_exec_requests will show which session holds locks involved in the blocking chain. → Yes

Comprehensive and Detailed Explanation with all Developing AI-Enabled Database Solutions documents : =

The first statement is Yes . The evidence is conclusive: the blocking session is sleeping , has open_transaction_count = 1 , and its last submitted command was BEGIN TRANSACTION UPDATE Sales.Orders . Microsoft specifically identifies a sleeping session with an open transaction as a classic blocking condition: modifications retain their exclusive locks until the transaction is committed or rolled back . The LCK_M_X waits and identical nonzero blocking_session_id values confirm that other requests are waiting for incompatible exclusive locks.

The second statement is No because READ_COMMITTED_SNAPSHOT is ON by default for SQL database in Microsoft Fabric . Under the default READ COMMITTED isolation level, reads therefore use row versioning rather than shared locks. Consequently, a normal SELECT can read the appropriate committed row version while an UPDATE holds locks, so the writer does not ordinarily block the reader. Microsoft explicitly documents RCSI as ON by default in SQL database in Microsoft Fabric .

The third statement is Yes in the context of blocking diagnostics. sys.dm_tran_locks exposes lock ownership through fields such as request_session_id , request_mode , and request_status , while sys.dm_exec_requests exposes active request and blocking_session_id information. Correlating these DMVs allows you to associate locks with sessions participating in a blocking chain. Microsoft specifically recommends sys.dm_tran_locks for determining which locks are currently held and sys.dm_exec_requests for identifying blocked and blocking sessions.

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 schema meets the security requirements for PII data. → Yes

Administrators of the Azure SQL server can see all the rows in dbo.CustomerProfiles when they use an application. → No

The masking rules will apply even when row-level security (RLS) filters out rows. → No

The first statement is Yes because the design combines two relevant SQL security controls for personally identifiable information: Dynamic Data Masking (DDM) on sensitive columns such as FullName, EmailAddress, and PhoneNumber, and Row-Level Security (RLS) to restrict which rows a user can access based on RegionCode. Microsoft documents that DDM limits sensitive data exposure for nonprivileged users , while RLS restricts row access according to the user executing the query. Together, these are valid and appropriate controls for protecting PII in Azure SQL Database.

The second statement is No . Administrative users can view unmasked data because administrative roles effectively have CONTROL, which includes UNMASK. However, that does not mean they automatically see all rows through the application query path defined by the RLS policy. The security policy filters rows based on SUSER_SNAME() and matching RegionCode, so row visibility is governed by the predicate unless the policy is altered or bypassed administratively. DDM and RLS solve different problems: DDM affects how returned values are shown, while RLS affects which rows are returned at all.

The third statement is No because masking only applies to data that is actually returned in the query result set. Microsoft describes DDM as hiding sensitive data in the result set of a query . If RLS filters a row out, that row is not returned, so there is nothing left for masking to act on. In other words, RLS eliminates inaccessible rows first from the user’s perspective, and DDM masks sensitive column values only on rows the user is allowed to see.

The best recommendation is B because the slow query filters on FleetId and returns LastUpdatedUtc , EngineStatus , and BatteryHealth . A nonclustered index with FleetId as the key column allows the optimizer to perform an index seek instead of a clustered index scan, and including the other selected columns makes the index covering , which reduces extra lookups and I/O. Microsoft’s SQL Server indexing guidance states that a nonclustered index with included columns can significantly improve performance when all query columns are available in the index, because the optimizer can satisfy the query directly from the index.

The query is:

SELECT VehicleId, LastUpdatedUtc, EngineStatus, BatteryHealth

FROM dbo.VehicleHealthSummary

WHERE FleetId = @FleetId

ORDER BY LastUpdatedUtc DESC;

Among the given choices, FleetId is the most important search argument because it appears in the WHERE predicate. Microsoft’s index design guidance recommends putting columns used for searching in the key and using nonkey included columns to cover the rest of the query efficiently.

Why the other options are weaker:

A is not appropriate because changing the clustered index to LastUpdatedUtc would not target the main filter predicate on FleetId, and a table can have only one clustered index.

C makes LastUpdatedUtc the key, which is poor for a query whose primary filter is FleetId.

D is not the right answer here because the query requirement does not specify only recent rows, and filtered indexes are meant for a well-defined subset; this option also uses a time-based expression that is not aligned to the stated query pattern.

Strictly speaking, the most optimal design for both filtering and ordering would usually be a composite key like (FleetId, LastUpdatedUtc), but since that is not one of the available options, B is the correct exam answer.