Which three code segments should you run in sequence? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

Question

Exhibit

Explanation

The question requires arranging SQL code segments to add a non-clustered, not enforced primary key to an existing table.

Approach. The correct interaction involves dragging three specific code segments into the 'Answer Area' and arranging them in the following sequence:

1. 'ALTER TABLE dbo.DimCustomer': This statement is necessary to modify the structure of an existing table, which in this case is to add a constraint. Since the initial CREATE TABLE statement did not define a primary key, we must use ALTER TABLE to add it afterward.
2. 'ADD CONSTRAINT PK_DimCustomer PRIMARY KEY NONCLUSTERED (CustomerKey)': This segment defines the primary key constraint named 'PK_DimCustomer' on the 'CustomerKey' column and specifies that it should be a NONCLUSTERED index. A non-clustered primary key is often chosen in data warehousing scenarios or when a different clustered index is desired for query performance.
3. 'NOT ENFORCED': This keyword, when used with primary key or unique constraints, indicates that the database system will not check for data integrity violations for existing data or new inserts. This can improve data loading performance in specific scenarios (like ETL processes for a data warehouse) where data integrity is guaranteed by upstream processes. The combination of NONCLUSTERED and NOT ENFORCED is a common pattern in Azure Synapse Analytics (formerly SQL DW) and similar data warehousing contexts.

Common mistakes.

• common_mistake. 1. Using 'DROP CONSTRAINT PK_DimCustomer': This is incorrect because the initial CREATE TABLE statement does not include a primary key, so there is no existing constraint 'PK_DimCustomer' to drop. It would cause an error.

1. Using 'ADD CONSTRAINT PK_DimCustomer PRIMARY KEY CLUSTERED (CustomerKey)': While syntactically valid for adding a primary key, the inclusion of 'NOT ENFORCED' as a separate segment strongly suggests that the question is testing the knowledge of non-enforced constraints, which are typically non-clustered. If the intent was a clustered key, 'ENFORCED' would be more common, or no explicit enforcement keyword would be provided if ENFORCED was the default.
2. Using 'ENFORCED': This would ensure that data integrity is maintained by the database system, checking for uniqueness and nullability. However, the presence of 'NOT ENFORCED' indicates a specific scenario is being tested, likely one where performance during data loads is prioritized over immediate enforcement by the database itself.
3. Incorrect Order: Placing 'ADD CONSTRAINT' before 'ALTER TABLE' would be a syntax error, as 'ADD CONSTRAINT' is a clause of 'ALTER TABLE'. Placing 'NOT ENFORCED' before the 'ADD CONSTRAINT' clause would also be syntactically incorrect; the enforcement status follows the constraint definition.

Concept tested. SQL DDL (Data Definition Language) for modifying tables, specifically adding primary key constraints. This includes understanding the ALTER TABLE statement, ADD CONSTRAINT clause, PRIMARY KEY types (CLUSTERED vs. NONCLUSTERED), and constraint enforcement options (ENFORCED vs. NOT ENFORCED). It touches on concepts relevant to data warehousing and performance optimization in SQL databases.

No community discussion yet for this question.