Querying Data with Transact-SQL — Question 86

You need to create a database object that meets the following requirements:
✑ accepts a product identified as input
✑ calculates the total quantity of a specific product, including quantity on hand and quantity on order
✑ caches and reuses execution plan
✑ returns a value
✑ can be called from within a SELECT statement
✑ can be used in a JOIN clause
What should you create?

Answer options

• A. an extended stored procedure
• B. a user-defined scalar function
• C. a user-defined stored procedure that has an OUTPUT parameter
• D. a temporary table that has a columnstore index

Correct answer: B

Explanation

The correct choice is B, a user-defined scalar function, because it allows for returning a value, can be used in SELECT statements and JOIN clauses, and caches execution plans. Options A and C do not fit all the requirements since they cannot be called directly within SELECT statements as a returnable value, while D is not a function and does not provide the necessary output capability.