100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 160
A hosted service must share an external JSON schema with downstream systems. A test instead finds: A client breaks after column rename changes payload. Which underlying issue best explains this finding?
- Indexing or typed extraction does not match the JSON path
- JSON contract and projected field names were not stabilized
- The necessary search-key index is missing or unusable
- A non-Unicode column or conversion path lost characters
Explanation
Correct Answer
B:JSON contract and projected field names were not stabilized. It accounts for the specific observed failure: a client breaks after column rename changes payload. The intended solution is use explicit json shape in api and deterministic sql projection with the required setup.
Incorrect Answers
A:Indexing or typed extraction does not match the JSON path. This concerns property lookups require parsing every json document. The relevant SQL or system operation is JSON contract and projected field names were not stabilized.
C:The necessary search-key index is missing or unusable. This concerns point lookups scan an entire large rowset. The relevant SQL or system operation is JSON contract and projected field names were not stabilized.
D:A non-Unicode column or conversion path lost characters. This concerns names are replaced with question marks on insertion. The relevant SQL or system operation is JSON contract and projected field names were not stabilized.
Question 161
A query team needs a reusable read-only projection over complex joins. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)
- CREATE VIEW with explicit columns and no needless SELECT *
- Use a VIEW to encapsulate the SELECT
- Use inline TVF or grouped view depending on parameters
- Use module signing or EXECUTE AS with scoped permission
- Use a stable view contract with least-privilege access
Explanation
Correct Answers
A:This is the concrete configuration of the specified design: create view with explicit columns and no needless select *. The choice makes the implementation testable in the given workload.
B:Use a VIEW to encapsulate the SELECT. The implementation is create view with explicit columns and no needless select *; success can be checked by compare view output with approved base-table joins. The method addresses the stated data and workload constraint.
Incorrect Answers
C:Use inline TVF or grouped view depending on parameters. This concerns a reporting function returns one aggregated row per team. The relevant SQL or system operation is CREATE VIEW with explicit columns and no needless SELECT *.
D:Use module signing or EXECUTE AS with scoped permission. This concerns securely run a privileged operation for app callers. The relevant SQL or system operation is CREATE VIEW with explicit columns and no needless SELECT *.
E:Use a stable view contract with least-privilege access. This concerns mask administrative implementation complexity. The relevant SQL or system operation is CREATE VIEW with explicit columns and no needless SELECT *.
Question 162
A query team needs a reusable read-only projection over complex joins. The team selects: Use a VIEW to encapsulate the SELECT. Which implementation action puts this design into practice?
- Define SELECT with GROUP BY and typed return columns
- Expose only approved columns through a named VIEW
- Join inserted to deleted on key and write before/after values
- CREATE VIEW with explicit columns and no needless SELECT *
Explanation
Correct Answer
D:CREATE VIEW with explicit columns and no needless SELECT *. This is the concrete setup for use a view to encapsulate the select, and compare view output with approved base-table joins supplies a direct confirmation.
Incorrect Answers
A:Define SELECT with GROUP BY and typed return columns. This concerns a reporting function returns one aggregated row per team. The relevant SQL or system operation is CREATE VIEW with explicit columns and no needless SELECT *.
B:Expose only approved columns through a named VIEW. This concerns mask administrative implementation complexity. The relevant SQL or system operation is CREATE VIEW with explicit columns and no needless SELECT *.
C:Join inserted to deleted on key and write before/after values. This concerns recording old and new salaries. The relevant SQL or system operation is CREATE VIEW with explicit columns and no needless SELECT *.
Question 163
A query team needs a reusable read-only projection over complex joins. After implementing the design, which check most directly validates the expected behavior?
- Compare counts and totals to base data
- Compare view output with approved base-table joins
- Run success and rollback integration tests
- Compare output across two department IDs
Explanation
Correct Answer
B:Compare view output with approved base-table joins. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the view uses fragile select * or outdated assumptions.
Incorrect Answers
A:Compare counts and totals to base data. This concerns a reporting function returns one aggregated row per team. Instead, compare view output with approved base-table joins.
C:Run success and rollback integration tests. This concerns a monthly closing process performs several SQL steps as one operation. Instead, compare view output with approved base-table joins.
D:Compare output across two department IDs. This concerns a query returns top-priced products for a department. Instead, compare view output with approved base-table joins.
Question 164
A query team needs a reusable read-only projection over complex joins. A test instead finds: Reports disagree after underlying schema changes. Which underlying issue best explains this finding?
- The view uses fragile SELECT * or outdated assumptions
- The input contract used fragile delimited text
- Base table SELECT was granted unnecessarily
- Aggregation was applied after a many-to-many join
Explanation
Correct Answer
A:The view uses fragile SELECT * or outdated assumptions. It accounts for the specific observed failure: reports disagree after underlying schema changes. The intended solution is use a view to encapsulate the select with the required setup.
Incorrect Answers
B:The input contract used fragile delimited text. This is relevant when an overly long comma-separated string is parsed incorrectly. Here, the view uses fragile SELECT * or outdated assumptions.
C:Base table SELECT was granted unnecessarily. This concerns analyst can access private columns from source table. Here, the view uses fragile SELECT * or outdated assumptions.
D:Aggregation was applied after a many-to-many join. This concerns totals are inflated by duplicated join rows. Here, the view uses fragile SELECT * or outdated assumptions.
Question 165
A finance analyst repeatedly calculates tax for one amount. Which database design or engineering approach most directly meets this requirement?
- Enforce the rule with a constraint where expressible
- Use an appropriate scalar user-defined function
- Use idempotent DDL guarded by catalog checks where appropriate
- Use parameterized SQL to avoid injection
Explanation
Correct Answer
B:Use an appropriate scalar user-defined function. The implementation is create function with typed scalar input and return; success can be checked by test representative tax boundary cases. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Enforce the rule with a constraint where expressible. This concerns rejecting a prohibited change consistently. Instead, use an appropriate scalar user-defined function.
C:Use idempotent DDL guarded by catalog checks where appropriate. This concerns deterministic schema creation every deployment. Instead, use an appropriate scalar user-defined function.
D:Use parameterized SQL to avoid injection. This concerns a stored procedure reads user input in a search filter. Instead, use an appropriate scalar user-defined function.
Question 166
A finance analyst repeatedly calculates tax for one amount. The team selects: Use an appropriate scalar user-defined function. Which implementation action puts this design into practice?
- CREATE FUNCTION returning one parameterized SELECT
- CREATE FUNCTION with typed scalar input and return
- Define SELECT with GROUP BY and typed return columns
- Declare typed procedure parameters and avoid concatenated dynamic SQL
Explanation
Correct Answer
B:CREATE FUNCTION with typed scalar input and return. This is the concrete setup for use an appropriate scalar user-defined function, and test representative tax boundary cases supplies a direct confirmation.
Incorrect Answers
A:CREATE FUNCTION returning one parameterized SELECT. This concerns returning a set of eligible orders for one customer. The relevant SQL or system operation is CREATE FUNCTION with typed scalar input and return.
C:Define SELECT with GROUP BY and typed return columns. This concerns a reporting function returns one aggregated row per team. The relevant SQL or system operation is CREATE FUNCTION with typed scalar input and return.
D:Declare typed procedure parameters and avoid concatenated dynamic SQL. This concerns a stored procedure reads user input in a search filter. The relevant SQL or system operation is CREATE FUNCTION with typed scalar input and return.
Question 167
A finance analyst repeatedly calculates tax for one amount. After implementing the design, which check most directly validates the expected behavior?
- Fetch adjacent pages and test for overlap
- Force a runtime error and inspect durable rows
- Test representative tax boundary cases
- Repeat test with fixed @AsOfUtc input
Explanation
Correct Answer
C:Test representative tax boundary cases. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that independent copied tax expressions diverge.
Incorrect Answers
A:Fetch adjacent pages and test for overlap. This concerns exposing pagination to API callers. Instead, test representative tax boundary cases.
B:Force a runtime error and inspect durable rows. This concerns all steps to roll back on error. Instead, test representative tax boundary cases.
D:Repeat test with fixed @AsOfUtc input. This concerns a function's results depend on current utc time. Instead, test representative tax boundary cases.
Question 168
A finance analyst repeatedly calculates tax for one amount. A test instead finds: Reports disagree on rounding behavior. Which underlying issue best explains this finding?
- Transaction handling was incomplete
- The module security boundary was overprivileged
- Base table SELECT was granted unnecessarily
- Independent copied tax expressions diverge
Explanation
Correct Answer
D:Independent copied tax expressions diverge. It accounts for the specific observed failure: reports disagree on rounding behavior. The intended solution is use an appropriate scalar user-defined function with the required setup.
Incorrect Answers
A:Transaction handling was incomplete. This concerns early statements commit despite later failure. Here, independent copied tax expressions diverge.
B:The module security boundary was overprivileged. This concerns execution grants administrator-like access widely. Here, independent copied tax expressions diverge.
C:Base table SELECT was granted unnecessarily. This concerns analyst can access private columns from source table. Here, independent copied tax expressions diverge.
Question 169
An API must return a set of eligible orders for one customer. Which database design or engineering approach most directly meets this requirement?
- Use a table-valued parameter to pass structured rows
- Expose a parameterized inline TVF for filtering
- Use an inline table-valued function when suitable
- Use inline TVF or grouped view depending on parameters
Explanation
Correct Answer
C:Use an inline table-valued function when suitable. The implementation is create function returning one parameterized select; success can be checked by compare rowsets for eligible and ineligible customers. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use a table-valued parameter to pass structured rows. This concerns an application submits a list of ids for one batch operation. Instead, use an inline table-valued function when suitable.
B:Expose a parameterized inline TVF for filtering. This concerns a query returns top-priced products for a department. Instead, use an inline table-valued function when suitable.
D:Use inline TVF or grouped view depending on parameters. This concerns a reporting function returns one aggregated row per team. Instead, use an inline table-valued function when suitable.
Question 170
An API must return a set of eligible orders for one customer. The team selects: Use an inline table-valued function when suitable. Which implementation action puts this design into practice?
- CREATE FUNCTION returning one parameterized SELECT
- CREATE FUNCTION with typed scalar input and return
- Configure a controlled execution context and avoid blanket role grants
- Define SELECT with GROUP BY and typed return columns
Explanation
Correct Answer
A:CREATE FUNCTION returning one parameterized SELECT. This is the concrete setup for use an inline table-valued function when suitable, and compare rowsets for eligible and ineligible customers supplies a direct confirmation.
Incorrect Answers
B:CREATE FUNCTION with typed scalar input and return. This concerns a finance analyst repeatedly calculates tax for one amount. The relevant SQL or system operation is CREATE FUNCTION returning one parameterized SELECT.
C:Configure a controlled execution context and avoid blanket role grants. This concerns securely run a privileged operation for app callers. The relevant SQL or system operation is CREATE FUNCTION returning one parameterized SELECT.
D:Define SELECT with GROUP BY and typed return columns. This concerns a reporting function returns one aggregated row per team. The relevant SQL or system operation is CREATE FUNCTION returning one parameterized SELECT.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests