A real-time analytics application uses a memory-optimized table to store recent sensor readings. The table receives thousands of inserts per second and also runs analytical queries that aggregate readings over the last hour. The table currently has a nonclustered hash index on SensorID. You need to improve the performance of the analytical queries without affecting the insert throughput. What should you implement?

Replace the hash index with a nonclustered range index on SensorID.

Create a separate disk-based table for analytics and synchronize it with the memory-optimized table.

Add a nonclustered columnstore index on the memory-optimized table.

Add a nonclustered rowstore index on the timestamp column.

A social media platform needs to model user connections and efficiently query friends-of-friends relationships. The data volume is large, and queries must traverse multiple relationship levels. Which design should you choose?

Create a normalized relational schema with a junction table and use multiple JOINs.

Create graph node and edge tables and use MATCH queries to traverse relationships.

Store connections as JSON arrays in a column and query with OPENJSON.

Create a self-referencing table with a FOREIGN KEY to itself and use recursive CTEs.

Which scenario is best suited for using an in-memory (memory-optimized) table?

A reporting database that is read-only

A high-throughput transaction processing system with frequent inserts and updates

A large data warehouse with complex analytical queries

What is the primary benefit of table partitioning?

To enforce referential integrity

To manage large tables by dividing them into smaller, more manageable segments

Microsoft Certified:SQL AI Developer Associate

Domain 1Objective 1

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

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 11–15

11application · medium
A product catalog stores product names that can be up to 200 characters, but most are under 50. The table is queried frequently by exact product name. You need to choose a data type that balances storage and query performance. What should you use for the ProductName column?
Select an answer first
12expert · hard
A real-time analytics application uses a memory-optimized table to store recent sensor readings. The table receives thousands of inserts per second and also runs analytical queries that aggregate readings over the last hour. The table currently has a nonclustered hash index on SensorID. You need to improve the performance of the analytical queries without affecting the insert throughput. What should you implement?
Select an answer first
13application · medium
A social media platform needs to model user connections and efficiently query friends-of-friends relationships. The data volume is large, and queries must traverse multiple relationship levels. Which design should you choose?
Select an answer first
14foundation · easy
Which scenario is best suited for using an in-memory (memory-optimized) table?
Select an answer first
15foundation · easy
What is the primary benefit of table partitioning?
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 3)

Questions 11–15

A product catalog stores product names that can be up to 200 characters, but most are under 50. The table is queried frequently by exact product name. You need to choose a data type that balances storage and query performance. What should you use for the ProductName column?

A social media platform needs to model user connections and efficiently query friends-of-friends relationships. The data volume is large, and queries must traverse multiple relationship levels. Which design should you choose?

Which scenario is best suited for using an in-memory (memory-optimized) table?

What is the primary benefit of table partitioning?

Keep studying — Design and develop database solutions