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.
Question 281
A regulated multi-tenant SQL system must protect files at rest, withhold protected salary plaintext from ordinary database engine access, and enforce tenant-specific row visibility. Which THREE distinct controls cover these requirements? Choose THREE.
- Dynamic Data Masking
- Row-Level Security
- Alert on suspicious pattern
- Column encryption key
- Transparent Data Encryption
- Always Encrypted
Explanation
Correct Answers
B:Row-Level Security applies predicate logic inside the database to filter or block access to rows according to security context; used in combination with Always Encrypted, Transparent Data Encryption, it handles a separate requirement of this combined workflow.
E:Transparent Data Encryption encrypts database storage at rest and decrypts pages for authorized engine operations; it does not hide plaintext from the engine; used in combination with Always Encrypted, Row-Level Security, it handles a separate requirement of this combined workflow.
F:Always Encrypted protects selected column values with client-side cryptography and keys outside ordinary server access, subject to query-operation limitations; used in combination with Row-Level Security, Transparent Data Encryption, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:Dynamic Data Masking alters query-result presentation for non-exempt users; it is not encryption or a strong isolation boundary for untrusted querying; it addresses a different step; the question calls for Row-Level Security and Transparent Data Encryption and Always Encrypted.
C:A metric or log alert rule evaluates collected telemetry and can initiate actions when a defined condition is met; the required capability here is Row-Level Security and Transparent Data Encryption and Always Encrypted, which this operation does not supply.
D:A column encryption key encrypts Always Encrypted column data, while its encrypted form is stored as database metadata; the alternative cannot substitute for Row-Level Security and Transparent Data Encryption and Always Encrypted under the given constraints.
Question 282
A reconciliation must return customers who have any unpaid invoices in one query and customers with no active contracts in another, avoiding duplication and NULL-sensitive anti-joins. Which TWO predicates are useful? Choose TWO.
- CROSS APPLY
- OUTER APPLY
- Scalar correlated subquery
- EXISTS correlated subquery
- NOT EXISTS correlated subquery
Explanation
Correct Answers
D:A correlated EXISTS predicate evaluates whether a related row exists for each outer record without materializing duplicate outer records; used in combination with NOT EXISTS correlated subquery, it handles a separate requirement of this combined workflow.
E:NOT EXISTS uses an anti-semi-join existence test and avoids the problematic NULL behavior of some NOT IN subqueries; used in combination with EXISTS correlated subquery, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:CROSS APPLY evaluates a table expression per outer row and includes only outer rows whose applied expression yields results; the missing behavior is EXISTS correlated subquery and NOT EXISTS correlated subquery; 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 EXISTS correlated subquery and NOT EXISTS correlated subquery for the requested result.
C:A scalar correlated subquery can compute one related value per outer row, provided the expression returns at most one value; for the stated conditions, the deciding feature is EXISTS correlated subquery and NOT EXISTS correlated subquery rather than this behavior.
Question 283
Technical manuals have titled sections and passages split across chunk boundaries. The indexer must respect section boundaries and repeat neighboring text in adjacent chunks, without using parent-document expansion at retrieval time. Which TWO chunking methods meet those exact requirements? Choose TWO.
- Semantic section chunking
- Preserve source identifiers separately
- Fixed-size token chunks
- Overlapping chunks
- Hierarchical parent-child chunks
Explanation
Correct Answers
A:Semantic chunking follows document structure or topic boundaries, making returned units more coherent than arbitrary cuts; used in combination with Overlapping chunks, it handles a separate requirement of this combined workflow.
D:Overlap repeats limited context between adjacent chunks to reduce information loss at boundaries, at added storage and embedding cost; used in combination with Semantic section chunking, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:Storing stable document and chunk identifiers alongside vectors supports traceability, updates, filtering, and safe content retrieval; the alternative cannot substitute for Semantic section chunking and Overlapping chunks under the given constraints.
C:Fixed-size chunking divides content into bounded segments, with boundaries that may not respect semantic topics; the condition in the stem is resolved by Semantic section chunking and Overlapping chunks, not by this feature alone.
E:Parent-child chunk mapping keeps fine-grained searchable chunks linked to larger source sections for context expansion; that function is not interchangeable with Semantic section chunking and Overlapping chunks for the requested result.
Question 284
An order lookup should see only committed rows while avoiding most shared-lock reader/writer blocking at statement scope. What is the right solution for this specific requirement?
- SERIALIZABLE
- READ COMMITTED SNAPSHOT
- REPEATABLE READ
- SNAPSHOT isolation
Explanation
Correct Answer
B:Statement-level row versioning under RCSI can allow committed reads without ordinary shared read locks.
Incorrect Answers
A:SERIALIZABLE applies range semantics that protect qualifying predicate ranges against conflicting concurrent insertions; for the stated conditions, the deciding feature is READ COMMITTED SNAPSHOT rather than this behavior.
C:REPEATABLE READ holds appropriate locks to protect previously read rows, but qualifying new rows may still appear on subsequent range queries; this differs from the READ COMMITTED SNAPSHOT requirement described in the scenario.
D:SNAPSHOT delivers transaction-consistent versioned reads while maintaining update conflict considerations; the required capability here is READ COMMITTED SNAPSHOT, which this operation does not supply.
Question 285
A team plans to switch old monthly partitions out of a large table and needs its relevant indexes organized compatibly. What is the right solution for this specific requirement?
- Partition function
- Partition switching
- Partition scheme
- Aligned partitioned index
Explanation
Correct Answer
D:Aligning indexes to the table's partitioning supports partition maintenance operations whose prerequisites require matching structure and boundaries.
Incorrect Answers
A:A partition function defines boundary points and maps data values to logical partitions without itself choosing storage destinations; the condition in the stem is resolved by Aligned partitioned index, not by this feature alone.
B:ALTER TABLE SWITCH changes partition ownership through metadata when structures, indexes and constraints satisfy switching prerequisites; the missing behavior is Aligned partitioned index; this mechanism does something else.
C:A partition scheme assigns the partitions defined by a function to filegroups and can be referenced by tables and indexes; that function is not interchangeable with Aligned partitioned index for the requested result.
Question 286
Two writers update Product then Inventory in one path and Inventory then Product in another, creating deadlock cycles. What should the developer implement?
- Enable appropriate row versioning
- Find open transaction owner
- Reduce lock footprint
- Consistent lock ordering
Explanation
Correct Answer
D:A shared acquisition order reduces circular-wait opportunities across concurrent operations.
Incorrect Answers
A:READ_COMMITTED_SNAPSHOT changes supported read behavior to row versions and can reduce reader/writer blocking; this differs from the Consistent lock ordering requirement described in the scenario.
B:Tracing blockers to a specific active transaction identifies the lock holder and avoids random query cancellation; the required capability here is Consistent lock ordering, which this operation does not supply.
C:Appropriate batching reduces simultaneous locks and transaction duration while preserving data correctness; the alternative cannot substitute for Consistent lock ordering under the given constraints.
Question 287
A service ticket includes both a model code and a descriptive symptom and neither retrieval signal alone meets quality targets. Which technique applies to this scenario?
- Boolean regulatory filter
- Exact serial-number retrieval
- Paraphrase retrieval
- Combined identifier and symptom retrieval
Explanation
Correct Answer
D:Hybrid search uses exact-term and semantic candidate lists so both precise identifiers and related descriptions influence ranking.
Incorrect Answers
A:A structured metadata predicate enforces eligibility; neither full-text relevance nor vector closeness is a security filter by itself; that function is not interchangeable with Combined identifier and symptom retrieval for the requested result.
B:Full-text or exact structured predicates can preserve the literal identifier, unlike semantic vectors that may cluster unrelated codes; for the stated conditions, the deciding feature is Combined identifier and symptom retrieval rather than this behavior.
C:Semantic embedding retrieval handles meaning-based paraphrases that lexical token matching can miss; it addresses a different step; the question calls for Combined identifier and symptom retrieval.
Question 288
A DBA needs cached aggregate resource usage for executed query plans to find statements consuming excessive CPU over their cache lifetime. What is the most suitable implementation?
- sys.dm_db_index_usage_stats
- sys.dm_os_wait_stats
- sys.dm_exec_query_stats
- sys.dm_exec_requests
Explanation
Correct Answer
C:sys.dm_exec_query_stats exposes aggregate statistics for queries represented in the plan cache, subject to cache eviction.
Incorrect Answers
A:Index usage DMVs provide counters for seeks, scans, lookups, and updates, with reset and interpretation limitations; the alternative cannot substitute for sys.dm_exec_query_stats under the given constraints.
B:sys.dm_os_wait_stats aggregates wait information at the server scope and must be interpreted alongside workload context; the condition in the stem is resolved by sys.dm_exec_query_stats, not by this feature alone.
D:sys.dm_exec_requests exposes live request information such as waits and blocking identifiers, rather than long-term history; that function is not interchangeable with sys.dm_exec_query_stats for the requested result.
Question 289
An application principal must be authorized within a particular SQL database rather than depending solely on instance-wide credentials. Which choice best meets these constraints?
- Contained database user
- Managed identity
- Least-privilege database role
- Microsoft Entra authentication
Explanation
Correct Answer
A:A contained database user represents an identity at database scope and can receive database permissions or role membership.
Incorrect Answers
B:A managed identity gives an eligible Azure workload an automatically managed service identity, with database access still requiring grants; this differs from the Contained database user requirement described in the scenario.
C:A custom database role can aggregate narrowly defined GRANT permissions and simplify managed membership; the required capability here is Contained database user, which this operation does not supply.
D:Microsoft Entra authentication supports identity-provider tokens for compatible SQL connections and database principal authorization; the alternative cannot substitute for Contained database user under the given constraints.
Question 290
A nightly ingestion pipeline must embed hundreds of thousands of static documents while enforcing API rate limits and checkpointing independently of write transactions. Which mechanism addresses the requirement?
- AI_GENERATE_EMBEDDINGS
- Database trigger with guarded work
- External batching worker
- Azure Function on change event
Explanation
Correct Answer
C:An external worker can batch requests, retry transient failures, and upsert stored vectors without holding long SQL insert transactions.
Incorrect Answers
A:AI_GENERATE_EMBEDDINGS calls an authorized external model definition to produce numeric embeddings from character input; the missing behavior is External batching worker; this mechanism does something else.
B:A DML trigger responds to table changes inside a database transaction, so remote model calls or expensive processing must be treated cautiously; that function is not interchangeable with External batching worker for the requested result.
D:A change-triggered Azure Function can generate and store embeddings after database changes, decoupling inference latency from OLTP writes; it addresses a different step; the question calls for External batching worker.