Practice Exam

Explanations for each answer:

• DROP CONSTRAINT PK_DimCustomer is incorrect. DROP CONSTRAINT is used to remove an existing constraint from a table. Since no primary key exists yet on the DimCustomer table, there is nothing to drop. This statement would result in an error.
• ADD CONSTRAINT PK_DimCustomer PRIMARY KEY NONCLUSTERED (CustomerKey) is correct. This statement adds the primary key constraint using NONCLUSTERED, which is required in Microsoft Fabric warehouses. Primary keys must be created as NONCLUSTERED because Fabric warehouses don't support clustered indexes on columnstore tables.
• NOT ENFORCED is correct. The NOT ENFORCED keyword is required when creating primary keys in Microsoft Fabric warehouses. This indicates that the constraint is for metadata purposes and query optimization but isn't physically enforced by the database engine, which is a characteristic of cloud data warehouse platforms.
• ALTER TABLE dbo.DimCustomer is correct. ALTER TABLE is required to add a primary key constraint to an existing table in Microsoft Fabric. Primary key constraints cannot be created inline within CREATE TABLE statements; they must be added afterward using ALTER TABLE.
• ADD CONSTRAINT PK_DimCustomer PRIMARY KEY CLUSTERED (CustomerKey) is incorrect. CLUSTERED primary keys are not supported in Microsoft Fabric warehouses. All primary keys must be NONCLUSTERED because the underlying storage uses columnstore indexes, which don't support clustered indexes.
• ENFORCED is incorrect. ENFORCED is not a valid keyword in Microsoft Fabric. All constraints must be created with NOT ENFORCED because Fabric warehouses don't physically enforce referential integrity constraints, relying instead on ETL/ELT processes to maintain data quality.