We are analyzing the query:
SELECT e.[WWI Employee ID],
e.Employee,
e.[Preferred Name],
gdr.[WWI Employee ID] AS [Direct Report ID] ,
gdr.Employee AS [Direct Report]
FROM Dimension.Employee AS e
OUTER APPLY Dimension.GetDirectReports(e.[Employee Key]) AS gdr;
Key concepts:
APPLY operator: Used to join a table with a table-valued function.
OUTER APPLY ensures all rows from the left (Employee) are returned, even if the function returns no rows (similar to LEFT JOIN).
Table-valued function (TVF): Returns a set of rows (unlike scalar functions, which return a single value).
The function executes once per row from the left table, not once for the whole query.
Evaluating each statement:
" Dimension.GetDirectReports is a scalar T-SQL function. "
No → It must be a table-valued function, because OUTER APPLY requires a table expression.
" The Dimension.GetDirectReports function will run only once when the query runs. "
No → With APPLY, the function runs for each row of Dimension.Employee.
" The output rows will include at least one row for each row in the Dimension.Employee table. "
Yes → Because of OUTER APPLY, every employee row is included, even if there are no direct reports (in that case, the columns from gdr will be NULL).
Final Answer:
Scalar function → No
Runs once per query → No
At least one row per Employee → Yes
