You are a database developer. You plan to design a database solution by using SQL Server 2008.
Your company operates a fulfillment warehouse. The company has the following business requirements:
• The packing of items to fulfill orders is processed as quickly as possible.
• When the product picker indicates that a box is full, the exact items list of the content and invoice are made available.
You plan to design a Transact-SQL user-defined-function. The function must perform the following tasks:
• Accept a list of value pairs named ProductID and Quantity as input.
• Retrieve the product price information from the ProductPrice table.
• Calculate the total price for each product.
• Calculate the total price for all products.
• Return the detailed and aggregated totals.
You need to design the function to meet these requirements. You want to achieve this goal by using the minimum amount of coding effort.
What should you do?
A. Create a multistatement table-valued function that accepts an XML parameter.
B. Create a multistatement table-valued function that accepts a table-valued parameter.
C. Create an inline table-valued function that accepts a table-valued parameter.
D. Create an inline table-valued function that accepts an XML parameter.
Correct Answer: C
Explanation/Reference:
It is clear that function should accepts a table-valued parameter, so we can cross out A and C options.
There is no doubt that we can use multistatement table-valued function that accepts a table-valued parameter:
CREATE TYPE dbo.OrderItemsList AS TABLE (ProductID int, Quantity int)
GO
CREATE TABLE ProductPrice
(
ProductID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Price money NOT NULL
)
GO
CREATE FUNCTION ufn_GetDetailedAndAggregatedTotalsMTVF (@OrderItemsList dbo.OrderItemsList READONLY)
RETURNS @Aggregates TABLE
(
ProductID int,
Quantity int,
Quantity int,
TotalPrice money
)
AS
BEGIN
INSERT INTO @Aggregates (ProductID, Quantity, TotalPrice)
SELECT OL.ProductID, OL.Quantity, OL.Quantity * PP.Price
FROM @OrderItemsList AS OL
INNER JOIN ProductPrice AS PP
ON OL.ProductID = PP.ProductID;
INSERT INTO @Aggregates (TotalPrice)
SELECT SUM(TotalPrice)
FROM @Aggregates;
RETURN;
END
GO
DECLARE @OrderItemsList dbo.OrderItemsList
INSERT INTO @OrderItemsList
VALUES (1, 2), (2, 1), (3, 3), (4, 2);
SELECT * FROM dbo.ufn_GetDetailedAndAggregatedTotalsMTVF(@OrderItemsList)
GO
Now we need to decide if it is possible to solve the task using one query. If yes, we can use inline table-valued function if not we should use multistatement tablevalued function.
CREATE FUNCTION ufn_GetDetailedAndAggregatedTotalsITVF (@OrderItemsList dbo.OrderItemsList READONLY)
RETURNS TABLE
AS
RETURN (
SELECT OL.ProductID, OL.Quantity, SUM(OL.Quantity * PP.Price) AS TotalPrice
FROM @OrderItemsList AS OL
INNER JOIN ProductPrice AS PP
ON OL.ProductID = PP.ProductID
GROUP BY GROUPING SETS(
(OL.ProductID, OL.Quantity)
,()
)
)
GO
DECLARE @OrderItemsList dbo.OrderItemsList
INSERT INTO @OrderItemsList
VALUES (1, 2), (2, 1), (3, 3), (4, 2);
SELECT * FROM dbo.ufn_GetDetailedAndAggregatedTotalsITVF(@OrderItemsList)
GO
In conclusion, if there is a possibility to use an inline table-valued function instead of a multistatement table-valued function we should always choose the first one.
Read more: http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/
