DP-203 Certification Practice Question #1

Question

You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement.

You need to alter the table to meet the following requirements:
✑ Ensure that users can identify the current manager of employees.
✑ Support creating an employee reporting hierarchy for your entire company.
✑ Provide fast lookup of the managers' attributes such as name and job title.
Which column should you add to the table?

Accepted Answer

The requirement is to identify the current manager, support an employee hierarchy, and enable fast lookup of manager attributes. In a dimensional model for dedicated SQL pool, that means storing a nullable self-referencing surrogate key to another employee row, not denormalizing the manager name. Because the existing employee surrogate key is an `IDENTITY`-based `int`, the manager reference should also be `int`. The suggested answer, overwhelming community vote, and Microsoft guidance on surrogate-key-based modeling all align on `ManagerEmployeeKey int`.

More DP-203 practice questions