You are a database developer on an instance of SQL Server. You have defined an inline table-valued function using the following statement:
CREATE FUNCTION udf_GetOrderDetails (@OrderID int)
RETURNS TABLE
AS
RETURN (
SELECT sh.OrderDate, sd.*
FROM SalesOrderHeader sh
INNER JOIN SalesOrderDetail sd
ON sh.SalesOrderID = sd.SalesOrderID
WHERE sd.SalesOrderID = @OrderID);
You have table named SalesOrderHistory that contains a SalesOrderID column. You want to query the SalesOrderHistory table and use the udf_GetOrderDetails function to return order details. All sales orders in the SalesOrderHistory table and the associated order details should be displayed, even if no details exist for a given order.
Which query should you use?
A. SELECT * FROM
FROM SalesOrderHistory h
CROSS APPLY udf_GetOrderDetails (h.SalesOrderID) AS d
B. SELECT h.*, d.*FROM SalesOrderHistory h
OUTER APPLY udf_GetOrderDetails (h.SalesOrderID) AS d ORDER BY h.SalesOrderID;
C. SELECT * FROM udf_GetOrderDetails(t.SalesOrderID)) as t;
D. SELECT h.*, d.*
INNER JOIN udf_GetOrderDetails(h.SalesOrderID) AS d
ON h.SalesOrderID = d.SalesOrderID
ORDER BY h.SalesOrderID;
Correct Answer: B
Explanation/Reference:
You should use the following query:
SELECT h.*, d.*FROM SalesOrderHistory hOUTER APPLY udf_GetOrderDetails (h.SalesOrderID) AS d ORDER BY h.SalesOrderID;
In this scenario, you wanted to join the results of a table-valued function to another table. You can accomplish this using the APPLY operator in the FROM clause of your query. The syntax is as follows:
SELECT column_list FROM outer_table {CROSS | OUTER} APPLY inner_table
The inner_table may specify another table or view, but may also specify a function that returns a table type. In this scenario, you specified the udf_GetOrderDetails function as the inner table. For each row in the query, the udf_GetOrderDetails function is called using the SalesOrderID from the outer table. If the function returns rows, these rows are joined with the outer table using UNION ALL and are available to the query. You should use OUTER APPLY to ensure that all rows in the outer table, SalesOrderHistory, are returned even if no order details exist. For rows in the SalesOrderHistory table that are not returned by the function, all order detail columns will be assigned a NULL value. When using the APPLY operator, the outer_table may also use a table-valued function, but it cannot pass the function columns to the inner_table as arguments.
You should not use the query that includes an inner join because this syntax is invalid. This statement will return the following error:
Msg 4104, Level 16, State 1, Line 12The multi-part identifier "h.SalesOrderID" could not be bound.
You should not use the query that uses a subquery because it attempts to reference t.SalesOrderID in the inner query. This statement will generate the following error:
Msg 4104, Level 16, State 1, Line 25The multi-part identifier "t.SalesOrderID" could not be bound.
You should not use the query that uses CROSS APPLY. A CROSS APPLY operator only returns rows that had rows returned by the udf_GetOrderDetails function.
In this scenario, you wanted to return all rows from the SalesOrderHistory table, even if they contained no order details. A CROSS APPLY would not return rows in the SalesOrderHistory table that did not contain order details.
