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 statement should
you use?
A.
CREATE TABLE Customer
(SourceID int NOT NULL IDENTITY,
CustomerID int NOT NULL IDENTITY,
CustomerName varchar(255) NOT NULL);
B.
CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);
C.
CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
D.
CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(SourceID, CustomerID));
Explanation:
Verified the answer as correct.
D
1
0
The correct answer should be “C”
Because the question is: “… the table has no duplicate CustomerID …”, so that means that the CustomerID should be UNIQUE. Am I right?
Thanks for your replay,
Sergey
0
0
It cant be ‘C’ because if you look the Source ID is Duplicated. So ‘C’ is wrong.
‘A’ is also incorrect due to the fact that Source ID is an IDENTITY.
‘B’ is plausable because Customer ID is a PK so no dups.
Finally D is plausable because “CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED”
I think its between B and D, yet B looks much better then D.
But maybe because of the sort part in D it makes it correct for this question.
0
0
Actually D is correct you will have a composite key.
Sorry for the confusion yes given answer is correct. D
0
0
d
1
0