Microsoft DP-600: Is Dimension.GetDirectReports a Scalar T-SQL Function?

Discover the truth about Dimension.GetDirectReports in Microsoft DP-600. Learn if it’s a scalar T-SQL function, how many times it runs, and its impact on output rows.

Question

You have a Fabric tenant that contains a warehouse named WH1.

You run the following T-SQL query against WH1.

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;

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

Statements

• Dimension.GetDirectReports is a scalar T-SQL function.
• The Dimension.GetDirectReports function will run only once when the query runs.
• The output rows will include at least one row for each row in the Dimension.Employee table.

Answer

Dimension.GetDirectReports is a scalar T-SQL function: No

The Dimension.GetDirectReports function will run only once when the query runs: No

The output rows will include at least one row for each row in the Dimension.Employee table: Yes

Explanation

Dimension.GetDirectReports is a scalar T-SQL function: No
In the given query, Dimension.GetDirectReports is used with the OUTER APPLY operator, which indicates that it is a table-valued function, not a scalar function. Table-valued functions return a table of rows, while scalar functions return a single value.

The Dimension.GetDirectReports function will run only once when the query runs: No
The OUTER APPLY operator will execute the Dimension.GetDirectReports function for each row in the Dimension.Employee table. This means that the function will run multiple times, once for each employee, to retrieve their direct reports.

The output rows will include at least one row for each row in the Dimension.Employee table: Yes
The OUTER APPLY operator ensures that all rows from the Dimension.Employee table are included in the output, even if the Dimension.GetDirectReports function returns no rows for a particular employee. This is because OUTER APPLY performs a left outer join, preserving all rows from the left table (Dimension.Employee) and matching them with the rows returned by the function on the right side.

In summary, Dimension.GetDirectReports is a table-valued function that runs multiple times, once for each row in the Dimension.Employee table, and the output will include at least one row for each employee, even if they have no direct reports.

Microsoft DP-600 certification exam assessment practice question and answer (Q&A) dump including multiple choice questions (MCQ) and objective type questions, with detail explanation and reference available free, helpful to pass the Microsoft DP-600 exam and earn Microsoft DP-600 certification.