Microsoft Certified: SQL AI Developer DP-800 Dumps PDF

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.

PhoneRaw extraction → JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

PhoneNumerals → REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

WHERE condition → p.PhoneRaw

The first correct selection is JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) because the phone number is stored as a scalar JSON property . Microsoft documents that JSON_VALUE extracts a scalar value from JSON text, whereas JSON_QUERY is intended for JSON objects or arrays.

The second selection is REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) . The regular expression [^0-9] matches every character that is not a digit . Replacing all such characters with an empty string converts a formatted value such as:

+000 000-000-0000

into:

0000000000000

Microsoft documents that REGEXP_REPLACE returns the source string after occurrences matching the regular expression are replaced, and it applies to Azure SQL Database.

The final blank is p.PhoneRaw , producing:

WHERE p.PhoneRaw IS NOT NULL;

This excludes customers that do not have a phone value while preserving one output row for each customer that does.

The completed query is:

WITH PhoneCTE AS

(

SELECT DISTINCT

c.CustomerId,

JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) AS PhoneRaw

FROM dbo.Customers AS c

)

SELECT

p.CustomerId,

REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) AS PhoneNumerals

FROM PhoneCTE AS p

WHERE p.PhoneRaw IS NOT NULL;

Final drag-and-drop selections:

First target: JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

Second target: REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

Third target: p.PhoneRaw