Question 330
A maintenance procedure needs one elevated table permission while ordinary callers must not receive that permission directly. Which approach should the team select?
- Group-based database access
- Certificate-signed SQL module
- Dedicated managed identity
- Database role membership
Explanation
Correct Answer
B:Signing an SQL module can attach narrowly scoped certificate-associated permissions during execution without broad user privilege escalation.
Incorrect Answers
A:Group-backed principals allow permissions to be managed through authorized group membership while database grants remain scoped; the missing behavior is Certificate-signed SQL module; this mechanism does something else.
C:A dedicated managed identity offers a workload principal whose token authorization can be controlled independently of human account lifecycle; for the stated conditions, the deciding feature is Certificate-signed SQL module rather than this behavior.
D:A database role collects permissions for members and reduces the need to duplicate identical grants on individual principals; it addresses a different step; the question calls for Certificate-signed SQL module.
Question 331
An Azure-hosted SQL-to-model application must use its assigned platform-managed workload identity to obtain AI service tokens, with no separately managed client secret, and must encrypt traffic to the API endpoint. Which TWO controls directly meet those requirements? Choose TWO.
- HTTPS/TLS endpoint
- Scoped token authorization
- Azure Key Vault
- Azure Monitor alerts
- Managed identity for model endpoint
Explanation
Correct Answers
A:TLS encrypts data in transit and authenticates the server endpoint, distinct from authorization to specific database rows; used in combination with Managed identity for model endpoint, it handles a separate requirement of this combined workflow.
E:A managed identity can obtain service tokens for supported model endpoints when assigned the appropriate resource permissions; used in combination with HTTPS/TLS endpoint, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:Server-side token validation and authorization check claims and permissions rather than treating any well-formed bearer token as sufficient; the alternative cannot substitute for HTTPS/TLS endpoint and Managed identity for model endpoint under the given constraints.
C:Key Vault secures secrets and cryptographic materials through authenticated access and audit controls instead of embedding values in repository files; the condition in the stem is resolved by HTTPS/TLS endpoint and Managed identity for model endpoint, not by this feature alone.
D:Azure Monitor evaluates selected signals against alert rules and can notify or trigger responses; it is not a row-authorization mechanism; the missing behavior is HTTPS/TLS endpoint and Managed identity for model endpoint; this mechanism does something else.
Question 332
A document ranked 1st in keyword results but absent in vector results must receive a positive contribution from the keyword list alone. Which database mechanism should be chosen?
- Rank fusion without score calibration
- Dual-list presence
- Reciprocal-rank contribution
- Higher rank position
Explanation
Correct Answer
C:RRF sums reciprocal-rank terms for each ranking list where a document appears, so missing-list contributions are simply absent.
Incorrect Answers
A:RRF works on rank positions, avoiding a need to equate arbitrary lexical and vector score magnitudes; for the stated conditions, the deciding feature is Reciprocal-rank contribution rather than this behavior.
B:RRF aggregates contributions across lists, so consistently highly ranked documents can outrank one-list specialists; it addresses a different step; the question calls for Reciprocal-rank contribution.
D:Reciprocal rank scoring gives a larger contribution to the higher-ranked item because its rank denominator is smaller; the required capability here is Reciprocal-rank contribution, which this operation does not supply.
Question 333
A continuous integration job must check DAB configuration validity before building an API container image. Which action is justified by these conditions?
- `dab validate`
- `dab add`
- `dab init`
- `dab start`
Explanation
Correct Answer
A:The validate command checks a DAB configuration against supported validation rules without serving the application.
Incorrect Answers
B:The add command registers an entity and its source and permissions in a DAB configuration; the missing behavior is `dab validate`; this mechanism does something else.
C:The init command scaffolds an initial DAB runtime configuration containing the chosen data-source and endpoint settings; that function is not interchangeable with `dab validate` for the requested result.
D:The start command runs the configured DAB service and hosts enabled endpoints for requests; for the stated conditions, the deciding feature is `dab validate` rather than this behavior.
Question 334
A writable view should convert a user-requested DELETE into marking an underlying record inactive instead. Which technique applies to this scenario?
- deleted logical table
- inserted logical table
- INSTEAD OF DELETE trigger
- AFTER UPDATE trigger
Explanation
Correct Answer
C:An INSTEAD OF DELETE trigger can replace a requested deletion with custom SQL logic, including soft-delete behavior.
Incorrect Answers
A:The deleted logical table contains prior versions of updated or deleted rows, supporting set-based old-versus-new comparisons; this differs from the INSTEAD OF DELETE trigger requirement described in the scenario.
B:The inserted logical table contains newly inserted or updated row versions available to a DML trigger and should be handled set-wise; the required capability here is INSTEAD OF DELETE trigger, which this operation does not supply.
D:An AFTER UPDATE trigger fires in response to UPDATE operations and can inspect affected rowsets, without being a scheduler; the condition in the stem is resolved by INSTEAD OF DELETE trigger, not by this feature alone.
Question 335
A support query needs the plain scalar `priority` property from JSON metadata so it can compare it to a string. What is the most appropriate design decision?
- ISJSON with ARRAY type constraint
- JSON_VALUE
- OPENJSON
- JSON_QUERY
Explanation
Correct Answer
B:JSON_VALUE extracts a single scalar at a path and is not intended to return entire JSON objects or arrays.
Incorrect Answers
A:ISJSON(value, ARRAY) tests both JSON syntax and the required top-level array shape on SQL versions that support its second type-constraint argument; that function is not interchangeable with JSON_VALUE for the requested result.
C:OPENJSON converts JSON input to a relational rowset and can use an explicit WITH schema for typed extraction; it addresses a different step; the question calls for JSON_VALUE.
D:JSON_QUERY extracts an object or array fragment and preserves its JSON structure as an expression result; this differs from the JSON_VALUE requirement described in the scenario.
Question 336
The application must express approximate top-k search using the current preferred SQL query syntax on a supported engine. Which action is justified by these conditions?
- FORCE_ANN_ONLY hint
- CREATE VECTOR INDEX
- VECTOR_DISTANCE exact ordering
- SELECT TOP (N) WITH APPROXIMATE
Explanation
Correct Answer
D:TOP with APPROXIMATE and an ordered vector-search distance permits eligible approximate index-based retrieval in current supported versions.
Incorrect Answers
A:FORCE_ANN_ONLY requests the approximate index strategy and has prerequisites including an appropriate index and approximate top-k query; the alternative cannot substitute for SELECT TOP (N) WITH APPROXIMATE under the given constraints.
B:CREATE VECTOR INDEX builds a vector retrieval index for eligible vector columns, with engine- and version-specific settings; the condition in the stem is resolved by SELECT TOP (N) WITH APPROXIMATE, not by this feature alone.
C:Sorting by VECTOR_DISTANCE calculates exact distances and can benchmark recall against indexed approximate results; the missing behavior is SELECT TOP (N) WITH APPROXIMATE; this mechanism does something else.
Question 337
A database exists but no schema project has been established, and engineers want a DACPAC model extracted from its current objects. What should be configured to meet this objective?
- Extract action
- Publish action
- DeployReport action
- Script action
Explanation
Correct Answer
A:Extract captures a database schema into a package suitable for inspection and project initialization, without being a full table-data export.
Incorrect Answers
B:Publish compares the DACPAC model with the target and performs required schema operations, with configurable deployment safeguards; this differs from the Extract action requirement described in the scenario.
C:DeployReport emits an XML description of deployment changes rather than applying them directly; the required capability here is Extract action, which this operation does not supply.
D:Script produces the deployment operations as SQL text for inspection before a separately authorized execution; the alternative cannot substitute for Extract action under the given constraints.
Question 338
Two compatible activity streams must be combined while preserving genuine duplicate rows for later event accounting. Which option satisfies the desired behavior most directly?
- UNION
- INTERSECT
- EXCEPT
- UNION ALL
Explanation
Correct Answer
D:UNION ALL concatenates the result sets without removing duplicate rows, unlike UNION's distinct behavior.
Incorrect Answers
A:UNION performs set combination with duplicate elimination across compatible query projections; the missing behavior is UNION ALL; this mechanism does something else.
B:INTERSECT returns distinct compatible rows appearing in both input result sets; that function is not interchangeable with UNION ALL for the requested result.
C:EXCEPT returns distinct rows from the first input that are not found in the second input; for the stated conditions, the deciding feature is UNION ALL rather than this behavior.
Question 339
Every new claim row that omits a review status should receive 'New' without executing an audit routine. What is the most suitable implementation?
- DEFAULT constraint
- AFTER trigger
- CHECK constraint
- INSTEAD OF trigger
Explanation
Correct Answer
A:A DEFAULT constraint supplies an omitted insertion value and is simpler than an event-triggered procedural program.
Incorrect Answers
B:An AFTER DML trigger executes in response to an eligible change and can inspect the inserted and deleted logical rowsets; the alternative cannot substitute for DEFAULT constraint under the given constraints.
C:A CHECK constraint is a declarative integrity rule enforced by the engine when qualifying rows are inserted or updated; the condition in the stem is resolved by DEFAULT constraint, not by this feature alone.
D:An INSTEAD OF trigger substitutes custom DML processing for the triggering operation, often making certain views writable; the missing behavior is DEFAULT constraint; this mechanism does something else.
Question 340
A reporting query filters by CustomerId and reads OrderDate and TotalDue; the team wants to avoid repeated key lookups without modifying the clustered key. Which approach should the team select?
- Clustered rowstore index
- Nonclustered covering index
- Filtered nonclustered index
- Clustered columnstore index
Explanation
Correct Answer
B:A nonclustered index can use CustomerId as its key and INCLUDE the projected fields, satisfying the query from the secondary index rather than fetching every base row.
Incorrect Answers
A:A clustered B-tree determines the rowstore table's data organization and supports efficient key-based access, but it is not a compressed analytics-oriented layout; for the stated conditions, the deciding feature is Nonclustered covering index rather than this behavior.
C:A filtered index covers only rows that satisfy its WHERE predicate, reducing maintenance and size while accelerating appropriately matched selective queries; this differs from the Nonclustered covering index requirement described in the scenario.
D:Clustered columnstore organizes and compresses values by column and uses batch-oriented processing for scanning and aggregating large analytical data sets; the required capability here is Nonclustered covering index, which this operation does not supply.