A retail company needs to track changes to product prices for auditing. They require the ability to query the price of a product as of a specific date in the past. The Product table has a PRIMARY KEY on ProductID and a CHECK constraint ensuring Price > 0. You need to design the table to support this requirement with minimal application changes. What should you implement?

Add a PERIOD column and SYSTEM_VERSIONING to the Product table to create a temporal table.

Add a ValidFrom and ValidTo column and update them manually in application code.

Create an audit trigger that inserts old rows into a separate ProductHistory table.

Create a view that unions the Product table with a snapshot table refreshed nightly.

What is a SEQUENCE in SQL Server?

A user-defined object that generates a sequence of numeric values

A user table must allow only one active email address per user, but a user may have multiple inactive emails. The table has a UserID primary key and an IsActive bit column. You need to enforce this rule at the database level. What should you implement?

A filtered UNIQUE index on Email where IsActive = 1.

A CHECK constraint that ensures Email is unique.

A UNIQUE constraint on the Email column.

Microsoft Certified:SQL AI Developer Associate

Domain 1Objective 1

Design and Implement Database Objects DP-800 Practice Questions (Page 7)

Part of the Design and develop database solutions domain, which accounts for 35–40% of the DP-800 exam. Microsoft does not publish an official question count, but from its 120-minute exam (~50–80 total, ~18–32 in this domain), expect 5–8 from this objective — we provide 47 practice questions to prepare you well beyond it. (estimate)

47questions here

10free pages

17concepts

35–40%of the exam

Questions 31–35

31application · medium
A retail company needs to track changes to product prices for auditing. They require the ability to query the price of a product as of a specific date in the past. The Product table has a PRIMARY KEY on ProductID and a CHECK constraint ensuring Price > 0. You need to design the table to support this requirement with minimal application changes. What should you implement?
Select an answer first
32foundation · easy
Which function is used to get the next value from a sequence?
Select an answer first
33foundation · easy
What is a SEQUENCE in SQL Server?
Select an answer first
34application · medium
A user table must allow only one active email address per user, but a user may have multiple inactive emails. The table has a UserID primary key and an IsActive bit column. You need to enforce this rule at the database level. What should you implement?
Select an answer first
35foundation · easy
Which column definition is most appropriate for storing a product price that must support two decimal places?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-800

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Microsoft. “DP-800” is a trademark of its owner, used for identification only.

Design and Implement Database Objects DP-800 Practice Questions (Page 7)

Questions 31–35

Which function is used to get the next value from a sequence?

What is a SEQUENCE in SQL Server?

A user table must allow only one active email address per user, but a user may have multiple inactive emails. The table has a UserID primary key and an IsActive bit column. You need to enforce this rule at the database level. What should you implement?

Which column definition is most appropriate for storing a product price that must support two decimal places?

Keep studying — Design and develop database solutions