Exam 70-765 topic 3 question 73 discussion

Actual exam question from Microsoft's 70-765

Question #: 73
Topic #: 3

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

Show Suggested Answer

Suggested Answer: D 🗳️

by t at March 1, 2019, 4:54 p.m.

Comments

Submit Cancel

Varad

Highly Voted 5 years, 1 month ago

D is correct. But it is with incomplete code. the entire code should be like this: CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID,CustomerID)) go

upvoted 12 times

...

t

Most Recent 5 years, 9 months ago

If you are just setting up the table, wouldn't you need to define the customer id column as a primary key column then set the primary key constraint?

upvoted 1 times

STH

5 years, 3 months ago

No because that would be incomplète. The question ask to ensure the unicity of CustomerID for each SourceID and clustered thé table for those two columns. So you need to define a PRIMARY KEY CLUSTERED on both SourceID and CustomerID (on that ordre). You cant define a PRIMARY KEY on two columns without the CONSTRAINT statement at the end of the CREATE TABLE

upvoted 1 times

...

Exam 70-765 All Questions

View all questions & answers for the 70-765 exam

Exam 70-765 topic 3 question 73 discussion

Comments

Varad

t

STH

SY0-701