70-463 · Question #135
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the
The correct answer is D. CREATE TABLE Customer. The correct CREATE TABLE statement must define a composite primary key on (SourceID, CustomerID) in that column order. This achieves both requirements simultaneously: (1) A composite PRIMARY KEY constraint enforces uniqueness of the CustomerID+SourceID combination, preventing dup
Question
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID . You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact-SQL stat.ement should you use?
Options
- ACREATE TABLE Customer
- BCREATE TABLE Customer
- CCREATE TABLE Customer
- DCREATE TABLE Customer
How the community answered
(42 responses)- A12% (5)
- B10% (4)
- C5% (2)
- D74% (31)
Explanation
The correct CREATE TABLE statement must define a composite primary key on (SourceID, CustomerID) in that column order. This achieves both requirements simultaneously: (1) A composite PRIMARY KEY constraint enforces uniqueness of the CustomerID+SourceID combination, preventing duplicate CustomerIDs within any given SourceID. (2) Because a PRIMARY KEY creates a clustered index by default, and the key is defined as (SourceID, CustomerID), the physical data is stored and ordered by SourceID first, then CustomerID - exactly as required. The other options either use incorrect key column order, define only a single-column key, or use a non-clustered index that would not enforce the required physical storage order.
Topics
Community Discussion
No community discussion yet for this question.