Question 50
A telemetry record should be expanded through OPENJSON into multiple sample rows, excluding source rows whose expansion is empty. What is the most appropriate design decision?
- LEFT JOIN
- OUTER APPLY
- CROSS APPLY
- INNER JOIN
Explanation
Correct Answer
C:CROSS APPLY joins each input row to the output of a correlated rowset expression, without retaining empty expansions.
Incorrect Answers
A:A LEFT OUTER JOIN preserves each left-side row and supplies NULL for missing right-side matches; the missing behavior is CROSS APPLY; this mechanism does something else.
B:OUTER APPLY preserves unmatched outer rows while evaluating a correlated table expression for each one; that function is not interchangeable with CROSS APPLY for the requested result.
D:An INNER JOIN returns qualifying pairs only and excludes rows lacking a match under its ON predicate; it addresses a different step; the question calls for CROSS APPLY.
Question 51
A high-volume order table is normally retrieved by an increasing order identifier; developers want the underlying rowstore rows organized by that key for narrow seeks. What is the most suitable implementation?
- Clustered rowstore index
- Nonclustered covering index
- Clustered columnstore index
- Filtered nonclustered index
Explanation
Correct Answer
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.
Incorrect Answers
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; the alternative cannot substitute for Clustered rowstore index under the given constraints.
C:Clustered columnstore organizes and compresses values by column and uses batch-oriented processing for scanning and aggregating large analytical data sets; the condition in the stem is resolved by Clustered rowstore index, not by this feature alone.
D:A filtered index covers only rows that satisfy its WHERE predicate, reducing maintenance and size while accelerating appropriately matched selective queries; the missing behavior is Clustered rowstore index; this mechanism does something else.
Question 52
A reporting query intentionally consumes most rows from an index and sequential traversal is more efficient than many seeks. Which approach should the team select?
- Index Scan
- Key Lookup
- Table Scan
- Index Seek
Explanation
Correct Answer
A:An Index Scan walks a substantial part of an index and can be appropriate for broad result sets.
Incorrect Answers
B:A Key Lookup obtains missing columns from the clustered data using keys retrieved from another index; covering may reduce this work; it addresses a different step; the question calls for Index Scan.
C:A Table Scan accesses heap data without using an index seek and may be reasonable when most rows are needed; this differs from the Index Scan requirement described in the scenario.
D:An Index Seek uses an index access path to locate qualifying key ranges, though residual predicates may still filter rows; the required capability here is Index Scan, which this operation does not supply.
Question 53
A government-issued document reference must be nonduplicated across accounts while AccountId remains the primary key. What should be configured to meet this objective?
- FOREIGN KEY validation
- CHECK (Quantity > 0)
- UNIQUE alternate identifier
- DEFAULT for CreatedAt
Explanation
Correct Answer
C:A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier.
Incorrect Answers
A:Foreign-key constraints protect relational references independently of the application that writes data; the condition in the stem is resolved by UNIQUE alternate identifier, not by this feature alone.
B:A CHECK predicate evaluates the supplied value at write time and rejects rows that violate its Boolean condition; the missing behavior is UNIQUE alternate identifier; this mechanism does something else.
D:A DEFAULT expression can supply an insertion-time value when the column is not explicitly populated; for the stated conditions, the deciding feature is UNIQUE alternate identifier rather than this behavior.
Question 54
A DAB configuration exists, but Products must now be exposed as a new named API entity backed by a database table. Which technique applies to this scenario?
- `dab validate`
- `dab init`
- `dab start`
- `dab add`
Explanation
Correct Answer
D:The add command registers an entity and its source and permissions in a DAB configuration.
Incorrect Answers
A:The validate command checks a DAB configuration against supported validation rules without serving the application; this differs from the `dab add` requirement described in the scenario.
B:The init command scaffolds an initial DAB runtime configuration containing the chosen data-source and endpoint settings; the required capability here is `dab add`, which this operation does not supply.
C:The start command runs the configured DAB service and hosts enabled endpoints for requests; the alternative cannot substitute for `dab add` under the given constraints.
Question 55
A GraphQL client wants each product's Id and Name but no Price or Description fields in that response. Which feature would solve the stated problem?
- GraphQL aggregation
- GraphQL mutation
- GraphQL field selection
- GraphQL relationship navigation
Explanation
Correct Answer
C:GraphQL projections let callers request only specified fields in a typed query, subject to endpoint permissions.
Incorrect Answers
A:Supported GraphQL aggregation fields calculate grouped or overall measures through the API instead of requiring all underlying rows at the client; that function is not interchangeable with GraphQL field selection for the requested result.
B:GraphQL mutations express write operations exposed by the configured schema and authorized by DAB roles; for the stated conditions, the deciding feature is GraphQL field selection rather than this behavior.
D:Configured GraphQL relationships permit nested traversal between related API entities within one query document; this differs from the GraphQL field selection requirement described in the scenario.
Question 56
A parameterized SELECT should be reusable in joins and expose a single relational query definition to the optimizer. Which SQL capability is the most direct fit?
- Scalar function
- Multi-statement table-valued function
- Stored procedure
- Inline table-valued function
Explanation
Correct Answer
D:An inline TVF returns the result of a single SELECT definition and is commonly optimized similarly to a parameterized view.
Incorrect Answers
A:A scalar UDF returns a single scalar value, although calling patterns and implementation determine its performance; the alternative cannot substitute for Inline table-valued function under the given constraints.
B:A multi-statement TVF declares a table return variable and populates it with multiple statements, subject to different optimizer behavior than inline TVFs; the condition in the stem is resolved by Inline table-valued function, not by this feature alone.
C:A stored procedure is an executable unit suited to procedural database operations; it is not directly a relational table expression; the missing behavior is Inline table-valued function; this mechanism does something else.
Question 57
A containerized API needs to connect to Azure SQL using a workload identity rather than a hard-coded administrative password. Which mechanism addresses the requirement?
- TLS termination
- Managed identity to database
- Validate JWT audience
- Per-role API operation policy
Explanation
Correct Answer
B:A managed identity supports token-based database sign-in if the SQL platform and database principal are configured appropriately.
Incorrect Answers
A:TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations; it addresses a different step; the question calls for Managed identity to database.
C:Checking a token's audience prevents treating tokens minted for unrelated resources as authorization to this endpoint; the required capability here is Managed identity to database, which this operation does not supply.
D:Role-level operation restrictions at the API boundary can separate read capabilities from write mutations without confusing authentication with authorization; the alternative cannot substitute for Managed identity to database under the given constraints.
Question 58
SQL Server terminated one of two transactions because both held locks needed by the other; the DBA needs the resource dependency cycle. Which approach should the team select?
- Consistent object access order
- Deadlock graph
- Blocking chain inspection
- Shorten transaction duration
Explanation
Correct Answer
B:A deadlock graph shows participants, resources, and the victim selection, clarifying the cyclic dependency.
Incorrect Answers
A:Accessing shared objects in a consistent order can prevent a circular lock acquisition pattern across concurrent transactions; the missing behavior is Deadlock graph; this mechanism does something else.
C:Analyzing blocking sessions and waits identifies which session owns the incompatible lock that others need; for the stated conditions, the deciding feature is Deadlock graph rather than this behavior.
D:Moving slow external work outside short database transactions reduces lock retention and contention windows; it addresses a different step; the question calls for Deadlock graph.
Question 59
Search must find a Spanish customer question relevant to an English technical paragraph without requiring manually translated keywords. Which option would you recommend?
- Text embedding model
- Multilingual embedding model
- Multimodal model
- Structured-output language model
Explanation
Correct Answer
B:A multilingual embedding model can represent semantically related text across supported languages in a shared vector space.
Incorrect Answers
A:An embedding model maps input content to numeric vectors for semantic comparison rather than producing a conversational answer; the required capability here is Multilingual embedding model, which this operation does not supply.
C:A multimodal model handles supported image and text inputs together, unlike text-only embedding or generation models; the condition in the stem is resolved by Multilingual embedding model, not by this feature alone.
D:A generation model with schema-constrained structured output can produce predictable field layouts for downstream validation; the missing behavior is Multilingual embedding model; this mechanism does something else.
Question 60
A correctness benchmark must rank all filtered vectors precisely even though an ANN index is present. Which option satisfies the desired behavior most directly?
- VECTOR_DISTANCE exact ordering
- SELECT TOP (N) WITH APPROXIMATE
- CREATE VECTOR INDEX
- FORCE_ANN_ONLY hint
Explanation
Correct Answer
A:Sorting by VECTOR_DISTANCE calculates exact distances and can benchmark recall against indexed approximate results.
Incorrect Answers
B:TOP with APPROXIMATE and an ordered vector-search distance permits eligible approximate index-based retrieval in current supported versions; it addresses a different step; the question calls for VECTOR_DISTANCE exact ordering.
C:CREATE VECTOR INDEX builds a vector retrieval index for eligible vector columns, with engine- and version-specific settings; this differs from the VECTOR_DISTANCE exact ordering requirement described in the scenario.
D:FORCE_ANN_ONLY requests the approximate index strategy and has prerequisites including an appropriate index and approximate top-k query; the required capability here is VECTOR_DISTANCE exact ordering, which this operation does not supply.