Question 80
An application team must address both issues before release. A stored report uses SELECT * after a schema expansion, unexpectedly exposing confidential columns to an API client. A report uses NOT IN against a subquery containing NULL and returns no rows even though unmatched IDs exist. Which course of action addresses the stated issues?
Explanation
Correct Answer
First, explicit projections avoid accidental information disclosure when the source table grows. Second, NULL in a NOT IN set can make the comparison unknown and unexpectedly filter all rows.
Incorrect Answers
Select explicitly authorized columns and make schema output changes subject to contract review.
Define a business-specific ORDER BY with a stable tie-breaker before applying TOP; Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records: explicit projections avoid accidental information disclosure when the source table grows.
Select explicitly authorized columns and make schema output changes subject to contract review; use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists: nULL in a NOT IN set can make the comparison unknown and unexpectedly filter all rows.



Developing AI-Enabled Database Solutions (DP-800) Practice Tests