What is the purpose of graph tables in SQL Server?

To enforce referential integrity

To improve performance of joins

To model and query complex relationships between entities

To store hierarchical data in a single table

A customer table in a CRM database stores customer information. Each customer must have a unique email address, but the email address can be updated. The table already has a primary key on CustomerID. You need to enforce the uniqueness of email addresses. What should you implement?

Create a nonclustered index on the Email column without the UNIQUE property.

Create a UNIQUE constraint on the Email column.

Create a PRIMARY KEY constraint on the Email column.

Create a CHECK constraint on the Email column to ensure it is not NULL.

A gaming company has a leaderboard table that is updated frequently and queried for top scores. The table is small (a few thousand rows) but experiences high concurrency and requires very low latency. You need to maximize performance for both updates and point lookups. What should you implement?

Create a temporary table for each session.

Create a regular table with a clustered columnstore index.

Create the table as a memory-optimized table with a nonclustered hash index on the primary key.

Create a regular table with a nonclustered index on the score column.

An order management system needs to generate order numbers that are unique across multiple tables (e.g., Orders and OrderHistory). The numbers must be sequential and not reused. You need a solution that does not require application code to manage the next number. What should you implement?

Use a UNIQUEIDENTIFIER column with NEWID() as default.

Create a trigger that calculates the maximum existing order number plus one.

Use an IDENTITY column in each table.

Create a SEQUENCE object and use it as the default for the order number column in both tables.

What is the purpose of partitioning an index?

To increase the size of the index

To enforce uniqueness on the index

To align the index with the table partitions for better manageability and performance

Microsoft Certified:SQL AI Developer Associate

Domain 1Objective 1

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

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 16–20

16foundation · easy
What is the purpose of graph tables in SQL Server?
Select an answer first
17application · medium
A customer table in a CRM database stores customer information. Each customer must have a unique email address, but the email address can be updated. The table already has a primary key on CustomerID. You need to enforce the uniqueness of email addresses. What should you implement?
Select an answer first
18application · medium
A gaming company has a leaderboard table that is updated frequently and queried for top scores. The table is small (a few thousand rows) but experiences high concurrency and requires very low latency. You need to maximize performance for both updates and point lookups. What should you implement?
Select an answer first
19application · medium
An order management system needs to generate order numbers that are unique across multiple tables (e.g., Orders and OrderHistory). The numbers must be sequential and not reused. You need a solution that does not require application code to manage the next number. What should you implement?
Select an answer first
20foundation · easy
What is the purpose of partitioning an index?
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.