Question 110
An application must detect that a row changed after it was read so it can reject a stale optimistic update. Which feature would solve the stated problem?
- ROWVERSION column
- SEQUENCE object
- Change Tracking
- IDENTITY property
Explanation
Correct Answer
A:ROWVERSION is an automatically changing binary token for row modifications, useful in equality comparisons for optimistic concurrency and not a clock timestamp.
Incorrect Answers
B:A SEQUENCE is a separately defined number generator that can be consumed from different tables and statements using NEXT VALUE FOR; the required capability here is ROWVERSION column, which this operation does not supply.
C:Change Tracking can tell a synchronization consumer which rows changed since a version; it does not, by itself, enforce a compare-and-swap update against the row version originally read.
D:IDENTITY automatically produces values for an inserted column, although applications must not assume gap-free numbering or transactional sequences; the condition in the stem is resolved by ROWVERSION column, not by this feature alone.
Question 111
A multilingual helpdesk uses shared embeddings and must never return another organization's content even when it is the nearest vector. What is the most appropriate design decision?
- Enforce tenant predicate
- Filter published state
- Apply effective-date policy
- Precision@5 metric
- Filter language metadata
Explanation
Correct Answer
A:Authorization-aware filtering restricts eligible search rows independently of similarity ranking and should be enforced server-side.
Incorrect Answers
B:A published-state predicate prevents unapproved drafts from entering the context supplied to the model; for the stated conditions, the deciding feature is Enforce tenant predicate rather than this behavior.
C:A date-range eligibility predicate excludes outdated or future policies independently of embedding quality; it addresses a different step; the question calls for Enforce tenant predicate.
D:Precision@5 quantifies relevance density among the top five, helping assess noisy retrieval; this differs from the Enforce tenant predicate requirement described in the scenario.
E:A language metadata predicate constrains the candidate corpus to the required locale before final answer construction; the required capability here is Enforce tenant predicate, which this operation does not supply.
Question 112
A rate calculation must convert a denominator of zero to NULL before division so a divide-by-zero error can be avoided. What is the best primary mechanism to use?
- COALESCE
- CASE expression
- TRY_CONVERT
- NULLIF
Explanation
Correct Answer
D:NULLIF returns NULL when its two expressions are equal, often used with division to handle zero denominators.
Incorrect Answers
A:COALESCE returns the first non-NULL expression from its ordered arguments; the alternative cannot substitute for NULLIF under the given constraints.
B:CASE evaluates conditional branches and yields a scalar expression selected from matching WHEN conditions; the condition in the stem is resolved by NULLIF, not by this feature alone.
C:TRY_CONVERT attempts type conversion and returns NULL for many invalid values where a regular CONVERT would fail; the missing behavior is NULLIF; this mechanism does something else.
Question 113
An organization must prevent the assistant's generic SQL execution tool from running DELETE statements regardless of the user's chat wording. What should the developer implement?
- MCP tool server
- Database service identity
- MCP client
- Authorization policy on tool calls
Explanation
Correct Answer
D:Tool-side authorization and command restrictions should enforce permitted operations independently of natural-language prompt instructions.
Incorrect Answers
A:An MCP server exposes tool capabilities to a compatible client; the tool's database permissions still depend on its backend identity; it addresses a different step; the question calls for Authorization policy on tool calls.
B:A managed or workload identity can acquire authorized tokens for compatible SQL access, requiring appropriate database grants; this differs from the Authorization policy on tool calls requirement described in the scenario.
C:An MCP client connects to configured servers and consumes advertised capabilities while respecting its security controls; the required capability here is Authorization policy on tool calls, which this operation does not supply.
Question 114
SQL must inspect a stored vector's supported property, such as dimension-related metadata, without exporting it to a Python service. Which action is justified by these conditions?
- VECTOR_DISTANCE
- VECTOR_SEARCH
- VECTOR_NORMALIZE
- VECTORPROPERTY
Explanation
Correct Answer
D:VECTORPROPERTY reads documented properties of a VECTOR value and is not itself a similarity search function.
Incorrect Answers
A:VECTOR_DISTANCE calculates an exact metric-specific distance and does not automatically use an approximate vector index; the missing behavior is VECTORPROPERTY; this mechanism does something else.
B:VECTOR_SEARCH provides vector-neighbor retrieval and can use supported ANN indexing under the appropriate query form; that function is not interchangeable with VECTORPROPERTY for the requested result.
C:VECTOR_NORMALIZE produces a norm-adjusted vector for supported vector types and normalization modes; for the stated conditions, the deciding feature is VECTORPROPERTY rather than this behavior.
Question 115
A supported Azure SQL scenario needs database changes published into an event pipeline for near-real-time consumers. Which choice best meets these constraints?
- Azure Functions SQL output binding
- Change Tracking
- Change event streaming
- Change Data Capture
- Azure Functions SQL trigger
Explanation
Correct Answer
C:Change event streaming sends supported database change events to a configured streaming destination, with platform-specific prerequisites.
Incorrect Answers
A:A SQL output binding can persist supported output values through configured SQL connectivity and binding semantics; the required capability here is Change event streaming, which this operation does not supply.
B:Change Tracking maintains lightweight change versions and keys instead of full historical before-and-after values; the alternative cannot substitute for Change event streaming under the given constraints.
D:CDC captures data changes in change tables for later consumption, including row images and operation information subject to configuration; the missing behavior is Change event streaming; this mechanism does something else.
E:The SQL trigger binding can drive Azure Functions from supported SQL change-tracking signals, subject to its trigger configuration; that function is not interchangeable with Change event streaming for the requested result.
Question 116
A policy assistant must return only records with Confidentiality = 'Public' before applying any search similarity. Which feature would solve the stated problem?
- Paraphrase retrieval
- Boolean regulatory filter
- Combined identifier and symptom retrieval
- Exact serial-number retrieval
Explanation
Correct Answer
B:A structured metadata predicate enforces eligibility; neither full-text relevance nor vector closeness is a security filter by itself.
Incorrect Answers
A:Semantic embedding retrieval handles meaning-based paraphrases that lexical token matching can miss; for the stated conditions, the deciding feature is Boolean regulatory filter rather than this behavior.
C:Hybrid search uses exact-term and semantic candidate lists so both precise identifiers and related descriptions influence ranking; this differs from the Boolean regulatory filter requirement described in the scenario.
D:Full-text or exact structured predicates can preserve the literal identifier, unlike semantic vectors that may cluster unrelated codes; the required capability here is Boolean regulatory filter, which this operation does not supply.
Question 117
A non-schema-bound view has stale metadata after an allowed underlying table change and its metadata should be refreshed. Which SQL capability is the most direct fit?
- sp_refreshview
- CREATE OR ALTER PROCEDURE
- DROP VIEW
- ALTER FUNCTION
Explanation
Correct Answer
A:sp_refreshview updates metadata for an eligible non-schema-bound view after referenced schema changes, rather than altering source tables.
Incorrect Answers
B:CREATE OR ALTER for supported modules makes a definition idempotent while preserving the object's logical identity; the missing behavior is sp_refreshview; this mechanism does something else.
C:DROP VIEW removes the view object, leaving its underlying tables intact subject to dependency constraints; that function is not interchangeable with sp_refreshview for the requested result.
D:ALTER FUNCTION changes a function's definition, subject to dependencies and function-kind restrictions; for the stated conditions, the deciding feature is sp_refreshview rather than this behavior.
Question 118
An API needs a restricted projected view, and any permitted update through that view must remain visible under its predicate. Which TWO view features address both requirements? Choose TWO.
- Indexed view
- SCHEMABINDING
- OUTPUT parameter
- WITH CHECK OPTION
- Projection view
Explanation
Correct Answers
D:WITH CHECK OPTION requires changes made through the view to remain visible under the view's WHERE condition when supported; used in combination with Projection view, it handles a separate requirement of this combined workflow.
E:A view can project selected columns and simplify controlled access when base-table permissions are restricted appropriately; used in combination with WITH CHECK OPTION, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:An indexed view materializes indexed results once the required unique clustered index and schema prerequisites are met; this differs from the WITH CHECK OPTION and Projection view requirement described in the scenario.
B:SCHEMABINDING binds an eligible view to referenced objects and prevents incompatible schema changes until the binding is addressed; the required capability here is WITH CHECK OPTION and Projection view, which this operation does not supply.
C:An OUTPUT parameter returns a variable value to the caller when the invocation and parameter declaration allow output; the alternative cannot substitute for WITH CHECK OPTION and Projection view under the given constraints.
Question 119
A query filters on TenantId and then on OrderDate; the team must choose a multicolumn key starting with the equality predicate. What is the best primary mechanism to use?
- Included index columns
- Filtered index predicate
- Composite index key order
- Index maintenance on fragmentation
Explanation
Correct Answer
C:Leading index-key columns determine which predicates can support a seek; key order matters for equality and range access.
Incorrect Answers
A:INCLUDE columns can satisfy output projections without expanding the seek key, often making a nonclustered index covering; that function is not interchangeable with Composite index key order for the requested result.
B:A WHERE filter on a nonclustered index narrows the indexed rowset to qualifying values when query predicates are compatible; for the stated conditions, the deciding feature is Composite index key order rather than this behavior.
D:Reorganizing or rebuilding appropriate indexes addresses physical fragmentation and may improve access efficiency, but should follow measured need; this differs from the Composite index key order requirement described in the scenario.
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.