Querying Data with Transact-SQL — Question 192

You need to create a database object that meets the following requirements:
✑ accepts a product identifies 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 table-valued function
• C. a user-defined stored procedure that has an OUTPUT parameter
• D. a memory-optimized table that has updated statistics

Correct answer: B

Explanation

The correct answer is B, a user-defined table-valued function, because it can return a value, be called within SELECT statements, and used in JOIN clauses while also meeting the other requirements. Options A and C do not fit all the criteria since they cannot be used directly in a SELECT statement and do not return a value in the way specified. Option D is irrelevant as it refers to a table structure rather than a callable function.