Implement Programmability Objects DP-800 Practice Questions (Page 4)

Questions 16–20

1. 16application · medium

   An inventory system has a stored procedure that inserts new products. The procedure uses a scalar function to generate a product code. You need to add a validation step that ensures the product code is unique before the insert, and if not, the procedure should raise an error. The validation must be performed only when the stored procedure is used, not for direct inserts. What should you do?

   • AAdd the uniqueness check inside the stored procedure before the INSERT.
   • BCreate a view that filters out duplicate product codes.
   • CCreate a scalar function that checks uniqueness and call it from the application code.
   • DCreate an AFTER INSERT trigger that checks uniqueness and rolls back if violated.
   Reveal Solution Discussion Select an answer first
2. 17expert · hard

   A company has a reporting database where the underlying tables are frequently modified by developers. Analysts need a stable interface that always shows current data and can be used in their own ad-hoc queries. The developers also need to be able to update the underlying tables without breaking the analysts' queries. You need to design a solution that minimizes maintenance and provides the required stability. What should you do?

   • ACreate a view that selects the required columns and instruct developers to avoid breaking changes
   • BCreate a table-valued function that returns the data
   • CCreate a trigger that copies data to a separate reporting table
   • DCreate a stored procedure that returns the data and have analysts call it
   Reveal Solution Discussion Select an answer first
4. 18expert · hard

   A stored procedure calculates order totals and needs to apply a complex discount rule that is also used in several other queries. The rule must be maintainable in one place. However, the stored procedure is performance-critical, and the rule involves a lookup to a discount table. You need to balance maintainability and performance. What should you do?

   • ACreate a view that calculates the discount and join to it
   • BInline the discount logic in the stored procedure and duplicate it in the queries
   • CCreate a scalar function for the discount rule and call it from the stored procedure and queries
   • DCreate a table-valued function that returns the discount amount for a set of orders
   Reveal Solution Discussion Select an answer first
5. 19application · medium

   A team of data analysts needs to run ad-hoc queries against a set of tables that are frequently restructured. You want to minimize the impact of schema changes on their queries by providing a stable interface. The analysts need to be able to filter and join the data themselves. What should you create?

   • AA stored procedure that returns the data
   • BA table-valued function that requires a parameter
   • CA view that provides a stable column set over the underlying tables
   • DA scalar function that returns a single value
   Reveal Solution Discussion Select an answer first
6. 20expert · hard

   A reporting application needs to retrieve data that requires a parameter (e.g., a date range) and must be able to join the result with other tables. The data volume is large, and performance is important. You need to choose between a table-valued function and a view with a filter. Which approach is more appropriate, and why?

   • AUse a multi-statement table-valued function because it can be indexed
   • BUse an inline table-valued function because it can accept the parameter and the optimizer can inline it for better performance
   • CUse a view with a filter because it is always inlined and performs better
   • DUse a stored procedure that returns the data because it is always the fastest
   Reveal Solution Discussion Select an answer first