Question 270
A developer receives a generated SQL statement that appears plausible and needs to verify output on actual test data before adoption. Which database mechanism should be chosen?
- SQL endpoint authorization
- Fabric tenant feature settings
- Copilot in Fabric chat
- Execute and inspect results
Explanation
Correct Answer
D:Running the query against representative data and reviewing results validates behavior; an AI suggestion is not a substitute for execution.
Incorrect Answers
A:Database authorization still applies when queries are proposed by Copilot, so appropriate grants are required separately; this differs from the Execute and inspect results requirement described in the scenario.
B:Tenant-level Fabric settings can govern Copilot availability, with scope and capacity prerequisites depending on the specific feature; the required capability here is Execute and inspect results, which this operation does not supply.
C:Microsoft Copilot in Fabric offers AI-assisted authoring in supported Fabric workloads and remains subject to tenant and capacity configuration; the alternative cannot substitute for Execute and inspect results under the given constraints.
Question 271
An AI-assisted testing session needs restricted database connectivity only for a short maintenance window. What is the right solution for this specific requirement?
- Scoped temporary credentials
- Schema metadata only
- Parameterize generated SQL
- Synthetic test data
Explanation
Correct Answer
A:Short-lived, narrowly authorized credentials limit exposure relative to reusable administrative secrets embedded in tool configuration.
Incorrect Answers
B:Restricting context to schema metadata can support query drafting without exposing record contents to the model; for the stated conditions, the deciding feature is Scoped temporary credentials rather than this behavior.
C:Parameterized execution separates user data from executable SQL, reducing injection risk even if the initial query text was AI-assisted; it addresses a different step; the question calls for Scoped temporary credentials.
D:Synthetic records can reproduce structural and edge-case patterns while reducing the need to expose personal production data; this differs from the Scoped temporary credentials requirement described in the scenario.
Question 272
Every query on a common invoices table must enforce tenant ownership even if a developer forgets a WHERE TenantId predicate. What should the developer implement?
- RLS tenant filtering
- Masked response
- Encrypted sensitive column
- Column-level permissions
Explanation
Correct Answer
A:A database RLS policy supplies the filtering boundary across application query paths using supported security predicates.
Incorrect Answers
B:Dynamic Data Masking changes result visibility according to effective permissions while leaving encrypted-at-rest requirements separate; the condition in the stem is resolved by RLS tenant filtering, not by this feature alone.
C:Always Encrypted places decryption with approved clients and key stores rather than relying only on engine-side masking; the missing behavior is RLS tenant filtering; this mechanism does something else.
D:Column-level GRANT or DENY policies can scope SELECT privileges to designated columns where supported; that function is not interchangeable with RLS tenant filtering for the requested result.
Question 273
A nonclustered index finds matching keys, but the plan repeatedly fetches extra selected fields from the clustered index. What should be configured to meet this objective?
- Index Scan
- Table Scan
- Index Seek
- Key Lookup
Explanation
Correct Answer
D:A Key Lookup obtains missing columns from the clustered data using keys retrieved from another index; covering may reduce this work.
Incorrect Answers
A:An Index Scan walks a substantial part of an index and can be appropriate for broad result sets; it addresses a different step; the question calls for Key Lookup.
B:A Table Scan accesses heap data without using an index seek and may be reasonable when most rows are needed; this differs from the Key Lookup requirement described in the scenario.
C: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 Key Lookup, which this operation does not supply.
Question 274
A known-good plan must be favored for a repeatedly regressing query without modifying every calling application statement. Which option satisfies the desired behavior most directly?
- OPTION (RECOMPILE)
- OPTIMIZE FOR UNKNOWN
- Parameter-sensitive plan diagnosis
- Query Store plan forcing
Explanation
Correct Answer
D:Query Store plan forcing can stabilize a query on a captured plan, while the DBA must monitor continued validity and suitability.
Incorrect Answers
A:RECOMPILE can produce a fresh plan for the current parameter values, trading compilation cost for specialization; the missing behavior is Query Store plan forcing; this mechanism does something else.
B:OPTIMIZE FOR UNKNOWN directs estimation toward generalized statistics rather than a particular sniffed parameter value; that function is not interchangeable with Query Store plan forcing for the requested result.
C:Parameter-sensitive behavior stems from different data selectivities sharing plan assumptions; runtime and plan comparison can reveal the issue; for the stated conditions, the deciding feature is Query Store plan forcing rather than this behavior.
Question 275
A transactional inventory table needs a stable B-tree organization for common seeks on ItemId rather than full-table aggregation scans. Which approach should the team select?
- Clustered columnstore table
- Memory-optimized table
- Clustered rowstore table
- Heap table
Explanation
Correct Answer
C:A clustered rowstore index organizes records by its clustering key and suits indexed OLTP access patterns.
Incorrect Answers
A:A clustered columnstore provides compressed columnar storage for analytical scans; it is not tuned primarily around singleton row lookups; the required capability here is Clustered rowstore table, which this operation does not supply.
B:Memory-optimized tables use the In-Memory OLTP engine and specialized concurrency structures, distinct from disk-based rowstore indexes; the alternative cannot substitute for Clustered rowstore table under the given constraints.
D:A heap has no clustered index and stores rows without a clustered key order, although secondary indexes can still be added; the missing behavior is Clustered rowstore table; this mechanism does something else.
Question 276
No developer should bypass mandatory successful builds and reviewers by pushing a schema change straight to the release branch. Which database mechanism should be chosen?
- Pull request
- Merge conflict resolution
- Protected main branch
- Feature branch
Explanation
Correct Answer
C:Branch protection or rulesets impose merge policies, required checks, and review conditions in the repository.
Incorrect Answers
A:A pull request provides a controlled review and merge workflow for database-source changes; for the stated conditions, the deciding feature is Protected main branch rather than this behavior.
B:Resolving the conflicting source edits produces one coherent schema definition before a merged database project can be compiled; it addresses a different step; the question calls for Protected main branch.
D:A feature branch provides a separate line of changes for review and integration without directly changing the release branch; the required capability here is Protected main branch, which this operation does not supply.
Question 277
A long-lived cached plan reflects old index and schema assumptions after a major database deployment and needs recompilation for the new shape. Which option would you recommend?
- Recompile after schema change
- REPEATABLE READ
- Read/write index tradeoff
- Histogram selectivity
- Covering index evaluation
Explanation
Correct Answer
A:Plan recompilation allows the optimizer to select operators using current metadata and available statistics.
Incorrect Answers
B:REPEATABLE READ holds appropriate locks to protect previously read rows, but qualifying new rows may still appear on subsequent range queries; the missing behavior is Recompile after schema change; this mechanism does something else.
C:Each secondary index adds maintenance and storage costs, so query benefit must be balanced against write amplification; that function is not interchangeable with Recompile after schema change for the requested result.
D:Statistics histograms describe value distributions used to estimate predicate cardinality and influence operator selection; for the stated conditions, the deciding feature is Recompile after schema change rather than this behavior.
E:Adding appropriate INCLUDE columns can make the access path covering and reduce repetitive base-row lookups, if write overhead is acceptable; it addresses a different step; the question calls for Recompile after schema change.
Question 278
Two inputs are joined where the outer side is tiny and the inner side has efficient keyed lookups. Which mechanism addresses the requirement?
- Nested Loops join
- Merge Join
- Hash Match join
- Sort operator
Explanation
Correct Answer
A:Nested Loops repeatedly probes an inner input for outer rows and can be efficient for selective joins with good indexes.
Incorrect Answers
B:Merge Join exploits compatible ordering for efficient matching, though sorting unordered inputs can add significant cost; the required capability here is Nested Loops join, which this operation does not supply.
C:Hash Match uses a hash table to match join keys and often suits large unordered joins at a memory cost; the alternative cannot substitute for Nested Loops join under the given constraints.
D:Sort produces ordered rows for ORDER BY or other operators and can become expensive or spill when memory is insufficient; the condition in the stem is resolved by Nested Loops join, not by this feature alone.
Question 279
An assistant proposes ALTER statements, but the release team must compare intended object definitions with the deployed database. Which option would you recommend?
- Protected branch rule
- Pull-request review
- Database schema comparison
- SqlPackage deployment report
Explanation
Correct Answer
C:Schema comparison shows differences between source models and targets, revealing unintended changes before deployment.
Incorrect Answers
A:Branch protection or rulesets enforce repository merge conditions independently of coding assistant recommendations; that function is not interchangeable with Database schema comparison for the requested result.
B:A reviewed pull request introduces human assessment and traceable approvals for database model changes; for the stated conditions, the deciding feature is Database schema comparison rather than this behavior.
D:A SqlPackage deployment report describes planned differences and supports review without immediately applying destructive changes; this differs from the Database schema comparison requirement described in the scenario.
Question 280
A DBA needs to test whether a least-privileged reporting user can actually query only its intended tenant rows. What is the best primary mechanism to use?
- EXECUTE AS for testing
- SESSION_CONTEXT
- Inline table-valued predicate function
- CREATE SECURITY POLICY
Explanation
Correct Answer
A:EXECUTE AS allows supported impersonation for evaluating effective permissions and RLS behavior, without permanently changing the policy.
Incorrect Answers
B:SESSION_CONTEXT exposes key-value session state that an RLS predicate can read, provided the application prevents identity spoofing; the condition in the stem is resolved by EXECUTE AS for testing, not by this feature alone.
C:RLS uses predicate functions to express per-row access rules evaluated by security policies, often with SCHEMABINDING; the missing behavior is EXECUTE AS for testing; this mechanism does something else.
D:CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled; that function is not interchangeable with EXECUTE AS for testing for the requested result.