Exam DP-300 All Questions

View all questions & answers for the DP-300 exam

Exam DP-300 topic 4 question 22 discussion

Actual exam question from Microsoft's DP-300

Question #: 22
Topic #: 4

A data engineer creates a table to store employee information for a new application. All employee names are in the US English alphabet. All addresses are locations in the United States. The data engineer uses the following statement to create the table.

You need to recommend changes to the data types to reduce storage and improve performance.
Which two actions should you recommend? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Change Salary to the money data type.
B. Change PhoneNumber to the float data type.
C. Change LastHireDate to the datetime2(7) data type.
D. Change PhoneNumber to the bigint data type.
E. Change LastHireDate to the date data type.

Show Suggested Answer

Suggested Answer: AE 🗳️
A: Money takes less space compared to VARCHAR(20)
E: Date takes less space compared to Datetime.
Reference:
https://docs.microsoft.com/eN-Us/sql/t-sql/data-types/data-types-transact-sql

by clownfishman at Dec. 13, 2020, 5:50 a.m.

Comments

Submit Cancel

Balti

Highly Voted 4 years, 6 months ago

Answers are correct. Date is smaller than DateTime. Changing salary to money means you can run calculations directly against the value improving performance e.g. WHERE salary > 50000. B C & D are definitely wrong for the same reasons.

upvoted 22 times

...

JohnCrawford

Highly Voted 4 years, 5 months ago

Answer is correct, but they should have used the smallmoney data type not money. ;-)

upvoted 10 times

...

HSQL

Most Recent 2 years, 3 months ago

C and E are the correct one. We only can be sure to reduce the space: money = 8 bytes < varchar(20) 20 bytes in the worst case float = 8 bytes < varchar(20) 20 bytes in the worst case datetime(7) = 7 bytes < datetime 8 bytes bigint = 8 bytes < varchar(20) 20 bytes in the worst case date = 3 bytes < datetime 8 bytes

upvoted 1 times

MVFGrant

8 months ago

C value (datetime2) takes more space than normal datetime

upvoted 1 times

...

OBIJUAN88

2 years, 1 month ago

A and E reduce more the space needed because you still having the salary in a varchar(20) kind. Is better to have LastHire in DateTime with 8 bytes and reduce the salary occupation to 8 than having it in 20 bytes.

upvoted 1 times

...

eric0718

3 years, 1 month ago

Selected Answer: AE

Answer is correct

upvoted 2 times

...

eric0718

3 years, 1 month ago

Answer is correct

upvoted 1 times

...

Drniyi4u

3 years, 5 months ago

Correct answer

upvoted 1 times

...

quermi

3 years, 7 months ago

https://docs.microsoft.com/eN-Us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15 MONEY 8 BYTES (-922,337,203,685,477.5808 to 922,337,203,685,477.5807). If your salary is greater than 1000.0000 then better than varchar(20) DATETIME=8 BYTES >DATETIME2(PRECISION7) 7 bytes for precision 3 or 4.

upvoted 2 times

...

nkav

4 years, 3 months ago

Answers are correct.The chosen data types use less storage. Money data type takes 8 bytes, less than Varchar(20) which is 20 bytes. Date takes up 3 bytes which is less than Datetime which takes up 8 bytes

upvoted 6 times

...

clownfishman

4 years, 6 months ago

is it because both are NULL-able fields?

upvoted 1 times

Jough_Blough

4 years, 5 months ago

Because A & E suggest changing the data type to a more appropriate data type. Null/not null has no bearing in this case. Aside: Address2/3 not null?

upvoted 2 times

...