Question 120
Engineers want to query centralized logs from SQL databases and container-hosted DAB services in one KQL workspace. Which option would you recommend?
- Log Analytics
- Query Store
- Azure Monitor metrics
- Application Insights
Explanation
Correct Answer
A:Log Analytics provides searchable aggregated telemetry when the relevant services emit data to that workspace.
Incorrect Answers
B:Query Store retains query plans and runtime histories, allowing statement-level regression comparison and supported plan-forcing remediation that generic request tracing lacks; the condition in the stem is resolved by Log Analytics, not by this feature alone.
C:Azure Monitor captures supported resource metrics and evaluates metric-based alert rules against them; the missing behavior is Log Analytics; this mechanism does something else.
D:Application Insights collects application telemetry and supports investigation of failed or slow service requests; that function is not interchangeable with Log Analytics for the requested result.
Question 121
Technical manuals contain headings and subsections; retrieved passages should usually correspond to meaningful topical sections. Which feature would solve the stated problem?
- Fixed-size token chunks
- Overlapping chunks
- Hierarchical parent-child chunks
- Semantic section chunking
Explanation
Correct Answer
D:Semantic chunking follows document structure or topic boundaries, making returned units more coherent than arbitrary cuts.
Incorrect Answers
A:Fixed-size chunking divides content into bounded segments, with boundaries that may not respect semantic topics; it addresses a different step; the question calls for Semantic section chunking.
B:Overlap repeats limited context between adjacent chunks to reduce information loss at boundaries, at added storage and embedding cost; this differs from the Semantic section chunking requirement described in the scenario.
C:Parent-child chunk mapping keeps fine-grained searchable chunks linked to larger source sections for context expansion; the required capability here is Semantic section chunking, which this operation does not supply.
Question 122
A transfer between accounts should either debit and credit together or roll back both changes on any failure. What is the best primary mechanism to use?
- Atomic transaction
- Pessimistic transaction locking
- Optimistic rowversion check
- Deadlock retry policy
Explanation
Correct Answer
A:A database transaction groups multiple modifications into one commit-or-rollback unit, preserving all-or-nothing behavior.
Incorrect Answers
B:Appropriate transaction isolation and update locks can serialize conflicting modifications during the critical reservation step; that function is not interchangeable with Atomic transaction for the requested result.
C:Comparing the original ROWVERSION token in UPDATE's WHERE clause allows a zero-row result to signal a stale write; for the stated conditions, the deciding feature is Atomic transaction rather than this behavior.
D:Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects; it addresses a different step; the question calls for Atomic transaction.
Question 123
Audit logic must report how many times a pattern appears within each free-text message. Which option satisfies the desired behavior most directly?
- REGEXP_INSTR
- REGEXP_LIKE
- REGEXP_REPLACE
- REGEXP_COUNT
Explanation
Correct Answer
D:REGEXP_COUNT counts matches of a regular expression in a source string.
Incorrect Answers
A:REGEXP_INSTR returns a match position for a supplied regular-expression pattern; the required capability here is REGEXP_COUNT, which this operation does not supply.
B:REGEXP_LIKE evaluates whether input text matches a regular expression and returns a predicate result; the alternative cannot substitute for REGEXP_COUNT under the given constraints.
C:REGEXP_REPLACE substitutes regex-matched substrings and returns the transformed text on supported platforms; the condition in the stem is resolved by REGEXP_COUNT, not by this feature alone.
Question 124
A consolidated history table runs wide aggregation queries over hundreds of millions of facts with limited transactional mutations. Which SQL capability is the most direct fit?
- Memory-optimized table
- Clustered columnstore table
- Heap table
- Clustered rowstore table
Explanation
Correct Answer
B:A clustered columnstore provides compressed columnar storage for analytical scans; it is not tuned primarily around singleton row lookups.
Incorrect Answers
A:Memory-optimized tables use the In-Memory OLTP engine and specialized concurrency structures, distinct from disk-based rowstore indexes; for the stated conditions, the deciding feature is Clustered columnstore table rather than this behavior.
C:A heap has no clustered index and stores rows without a clustered key order, although secondary indexes can still be added; this differs from the Clustered columnstore table requirement described in the scenario.
D:A clustered rowstore index organizes records by its clustering key and suits indexed OLTP access patterns; the required capability here is Clustered columnstore table, which this operation does not supply.
Question 125
A serverless embedding generator should react to tracked changes with minimal custom polling infrastructure. Which action is justified by these conditions?
- Scheduled full rebuild
- Change Data Capture
- Azure Functions SQL trigger
- Change Tracking
Explanation
Correct Answer
C:The SQL trigger binding can invoke a function for detected database changes and let the function handle inference and vector updates.
Incorrect Answers
A:A controlled rebuild recomputes vectors across the corpus, often into a new index or version, rather than mixing incompatible embedding spaces; the condition in the stem is resolved by Azure Functions SQL trigger, not by this feature alone.
B:CDC records change operations and row details for configured tables, supporting event processing and replay; the missing behavior is Azure Functions SQL trigger; this mechanism does something else.
D:Change Tracking identifies changed rows and versions efficiently, making it useful for incremental re-embedding without storing full change history; for the stated conditions, the deciding feature is Azure Functions SQL trigger rather than this behavior.
Question 126
An online-ordering session should avoid reading another session's uncommitted data using the database's conventional committed-data isolation level. Which technique applies to this scenario?
- READ COMMITTED
- SERIALIZABLE isolation
- READ UNCOMMITTED
- SNAPSHOT isolation
Explanation
Correct Answer
A:READ COMMITTED prevents dirty reads, with blocking or row-version behavior depending on the configured implementation.
Incorrect Answers
B:SERIALIZABLE is the strongest standard isolation level and can protect qualifying key ranges from concurrent phantom changes, increasing contention; the required capability here is READ COMMITTED, which this operation does not supply.
C:READ UNCOMMITTED allows access to uncommitted changes and can produce inconsistent values, so it is unsuitable for authoritative reports; the alternative cannot substitute for READ COMMITTED under the given constraints.
D:SNAPSHOT uses transaction-level row versions and checks write conflicts when updating rows another transaction has changed after the snapshot began, so the caller may need to retry; the condition in the stem is resolved by READ COMMITTED, not by this feature alone.
Question 127
Reports using READ COMMITTED share-lock reads are frequently blocked behind writers, and database policy allows versioned reads. Which database mechanism should be chosen?
- Enable appropriate row versioning
- Reduce lock footprint
- Find open transaction owner
- Consistent lock ordering
Explanation
Correct Answer
A:READ_COMMITTED_SNAPSHOT changes supported read behavior to row versions and can reduce reader/writer blocking.
Incorrect Answers
B:Appropriate batching reduces simultaneous locks and transaction duration while preserving data correctness; for the stated conditions, the deciding feature is Enable appropriate row versioning rather than this behavior.
C:Tracing blockers to a specific active transaction identifies the lock holder and avoids random query cancellation; it addresses a different step; the question calls for Enable appropriate row versioning.
D:A shared acquisition order reduces circular-wait opportunities across concurrent operations; this differs from the Enable appropriate row versioning requirement described in the scenario.
Question 128
A SELECT should construct a JSON array from three explicit values in a specific order without manual concatenation. Which feature would solve the stated problem?
- JSON_ARRAYAGG with NULL ON NULL
- FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
- JSON_OBJECT with ABSENT ON NULL
- JSON_ARRAY
Explanation
Correct Answer
D:JSON_ARRAY constructs an array value from its supplied scalar expressions.
Incorrect Answers
A:JSON_ARRAYAGG with NULL ON NULL retains SQL NULL inputs as JSON null elements; its usual ABSENT ON NULL behavior would omit those array slots; the alternative cannot substitute for JSON_ARRAY under the given constraints.
B:FOR JSON PATH with WITHOUT_ARRAY_WRAPPER removes the enclosing JSON array for a single-row result; use it only when cardinality and JSON validity are controlled; the condition in the stem is resolved by JSON_ARRAY, not by this feature alone.
C:JSON_OBJECT with ABSENT ON NULL suppresses keys whose SQL expressions are NULL; its default NULL ON NULL behavior would instead emit those keys with JSON null values; the missing behavior is JSON_ARRAY; this mechanism does something else.
Question 129
A view's dependencies must be protected from changes that would invalidate its schema-bound definition. Which database mechanism should be chosen?
- SCHEMABINDING
- WITH CHECK OPTION
- Indexed view
- Projection view
Explanation
Correct Answer
A:SCHEMABINDING binds an eligible view to referenced objects and prevents incompatible schema changes until the binding is addressed.
Incorrect Answers
B:WITH CHECK OPTION requires changes made through the view to remain visible under the view's WHERE condition when supported; this differs from the SCHEMABINDING requirement described in the scenario.
C:An indexed view materializes indexed results once the required unique clustered index and schema prerequisites are met; the required capability here is SCHEMABINDING, which this operation does not supply.
D:A view can project selected columns and simplify controlled access when base-table permissions are restricted appropriately; the alternative cannot substitute for SCHEMABINDING under the given constraints.
Question 130
Many executions use different CustomerId values; the app should avoid literal SQL concatenation and support safe parameter binding. Which option would you recommend?
- EXISTS semi-join
- TOP with ORDER BY
- SARGable date range
- Parameterized query
Explanation
Correct Answer
D:Parameterization separates values from SQL text, reducing injection risk and enabling plan reuse in many cases.
Incorrect Answers
A:EXISTS expresses a presence test and does not expand the outer result into one row per matching child; the missing behavior is Parameterized query; this mechanism does something else.
B:TOP constrained by ORDER BY selects highest-priority ordered rows, while tie handling requires a suitable secondary key when values match; that function is not interchangeable with Parameterized query for the requested result.
C:A half-open range on the raw indexed date can preserve seek opportunities and avoid a non-sargable function on the column; for the stated conditions, the deciding feature is Parameterized query rather than this behavior.