You are a database developer of a Microsoft SQL Server 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,

CustomerID int NOT NULL,

CustomerName varchar(255) NOT NULL

CONSTRAINT UQ_Customer UNIQUE

(SourceID, CustomerID));

B. CREATE TABLE Customer

(SourceID int NOT NULL UNIQUE,

CustomerID int NOT NULL UNIQUE,

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:

A PRIMARY KEY is a constraint that enforces entity integrity for a specified column or columns by using a unique index. Only one PRIMARY KEY constraint can be created for each table.

We need to use both SourceID and CustomerID, in that order, in the PRIMARY KEY constraint.

References:https://msdn.microsoft.com/en-us/library/ms188066.aspx