DP-900 Identify considerations for relational data on Azure Practice Test 7 — 15 Questions

Question 1

A company uses Azure SQL Database for an order management system. The 'Orders' table has millions of rows and is queried frequently with filters on OrderDate and CustomerID. The table currently has a clustered index on OrderID. Which action will most improve query performance for these frequent filters?

Accepted Answer

Create a non-clustered index on OrderDate and CustomerID. The frequent filters on OrderDate and CustomerID require a covering index that includes both columns. A non-clustered index on (OrderDate, CustomerID) allows SQL Server to perform an index seek for queries filtering on those columns, avoiding full clustered index scans on the existing clustered index on OrderID. This directly reduces I/O and improves query response times.

Answer

Create a clustered index on OrderDate

Answer

Create a non-clustered index on OrderID

Answer

Partition the table by CustomerID

Question 2

A company has multiple independent databases for different business units, each with low to moderate usage and varying workload patterns. They want to consolidate these databases into a single Azure SQL Database deployment option to share resources and reduce costs, while ensuring that databases do not starve each other of resources. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed for exactly this scenario: multiple databases with low to moderate usage and varying workload patterns. They allow databases to share a fixed pool of resources (eDTUs or vCores) while using built-in resource governance to prevent any single database from starving others, thus optimizing cost and performance.

Answer

Single database (DTU model)

Answer

Managed Instance

Answer

Database per server (hyperscale)

Question 3

A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries that filter on CustomerID and order by OrderDate are slow. The table currently has a clustered index on OrderID (the primary key). Which index strategy will best improve these queries?

Accepted Answer

C. Create a nonclustered index on (CustomerID, OrderDate).. Option C is correct because creating a nonclustered index on (CustomerID, OrderDate) directly supports the query's filter (WHERE CustomerID = ?) and sort (ORDER BY OrderDate) operations. This composite index allows SQL Server to seek on CustomerID and then retrieve rows in OrderDate order without a separate sort, eliminating the need for a full clustered index scan on OrderID. It is a covering index for this query pattern, significantly reducing I/O and CPU overhead.

Answer

A. Create a nonclustered index on OrderDate only.

Answer

B. Create a filtered index on CustomerID where Status = 'Active'.

Answer

D. Create a nonclustered index on OrderID and OrderDate.

Question 4

A company uses Azure SQL Database for an e-commerce system. The Orders table has millions of rows with a clustered index on OrderID (the primary key). Queries that filter on OrderDate and CustomerID to find recent orders for a specific customer are very slow. Which indexing strategy will most improve the performance of these queries?

Accepted Answer

Create a nonclustered composite index on (OrderDate, CustomerID). The query filters on both OrderDate and CustomerID, so a composite nonclustered index on (OrderDate, CustomerID) allows SQL Server to perform a single index seek to locate the matching rows without touching the clustered index until the final key lookup. This dramatically reduces I/O compared to scanning the entire clustered index or using multiple separate indexes.

Answer

Create a nonclustered index on OrderID only

Answer

Create separate nonclustered indexes on OrderDate and CustomerID

Answer

Create a clustered index on CustomerID instead of OrderID

Question 5

A company needs to store order data for an e-commerce platform. The system requires high concurrency, fast inserts, and the ability to enforce referential integrity between tables (e.g., Customers and Orders). Which Azure service should they use?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service that supports high concurrency, fast inserts, and enforces referential integrity through foreign key constraints. It provides ACID transactions and row-level locking to handle concurrent writes efficiently, making it ideal for e-commerce order processing where data consistency between Customers and Orders tables is critical.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Answer

Azure Data Lake Storage Gen2

Question 6

A company uses Azure SQL Database for an inventory management system. The Inventory table has millions of rows. Queries frequently filter on WarehouseID and then sort by LastUpdatedDate. The table currently has a clustered index on InventoryID (primary key). Which action will most improve query performance for these frequent filters?

Accepted Answer

Create a non-clustered index on (WarehouseID, LastUpdatedDate) INCLUDE (Quantity). A non-clustered index on (WarehouseID, LastUpdatedDate) allows the database engine to efficiently locate rows matching a specific WarehouseID and return them already sorted by LastUpdatedDate without accessing the clustered index (or with minimal lookup). Including the Quantity column as a non-key included column avoids key lookups for that column, further improving performance. Changing the clustered index to WarehouseID could cause fragmentation and is not ideal for uniqueness. A single-column index on LastUpdatedDate does not support the filter on WarehouseID. Partitioning by InventoryID does not help this query pattern.

Answer

Add a clustered index on WarehouseID

Answer

Create a non-clustered index on LastUpdatedDate only

Answer

Partition the table by InventoryID

Question 7

A company runs an e-commerce application on Azure SQL Database. The Orders table has millions of rows. Queries that filter on CustomerID and order by OrderDate DESC are slow. The table currently has a clustered index on OrderID (the primary key). Which indexing strategy will most improve performance for these queries?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) with included columns for other needed columns.. Option B is correct because creating a nonclustered index on (CustomerID, OrderDate DESC) with included columns allows the query to filter on CustomerID and sort by OrderDate in descending order using a single index seek, avoiding a sort operation. This leverages the index's key order to directly return rows in the desired order, which is critical for performance on large tables in Azure SQL Database.

Answer

Create a nonclustered index on CustomerID including OrderDate.

Answer

Rebuild the clustered index to be on CustomerID.

Answer

Create a nonclustered index on OrderDate.

Question 8

A company uses Azure SQL Database for an HR system. The Employees table has a clustered index on EmployeeID. Queries frequently filter on DepartmentID and LastName and also retrieve the Salary column. The table contains over a million rows. Which index strategy will most improve query performance for these filters?

Accepted Answer

A: Create a nonclustered index on (DepartmentID, LastName) INCLUDE (Salary). Option A is correct because it creates a covering nonclustered index on the filter columns (DepartmentID, LastName) and includes the Salary column as an included column. This allows the query to be fully satisfied by scanning only the nonclustered index pages, avoiding key lookups to the clustered index. The order of columns in the index key matches the query filter pattern, maximizing seek efficiency.

Answer

B: Create a nonclustered index on LastName only

Answer

C: Create a nonclustered index on DepartmentID and another nonclustered index on LastName

Answer

D: Change the clustered index to (DepartmentID, LastName)

Question 9

A company runs a financial application on Azure SQL Database. The Transactions table has a clustered columnstore index to support fast analytical queries on large historical datasets. However, the application also ingests a high volume of new transactions each second, and the columnstore index is causing performance degradation for these real-time inserts. The workload is hybrid (OLTP and OLAP). Which feature should the company implement to improve insert performance while still enabling efficient analytical queries on the table?

Accepted Answer

D: Convert the table to a rowstore heap with a nonclustered columnstore index. Option D is correct because converting the table to a rowstore heap with a nonclustered columnstore index allows the table to handle high-volume singleton inserts efficiently (rowstore heap) while still enabling fast analytical queries via the nonclustered columnstore index. This hybrid approach separates the OLTP insert path from the OLAP read path, avoiding the overhead of maintaining a clustered columnstore index during real-time ingestion.

Answer

A: In-memory OLTP

Answer

B: Elastic Query

Answer

C: Hyperscale service tier

Question 10

A university's enrollment system stores data in a single table with columns: EnrollmentID, StudentID, StudentName, CourseID, CourseName, and Grade. Students can take multiple courses, and each course has multiple students. The team notices data redundancy: StudentName is repeated for each enrollment of the same student, and CourseName is repeated for each enrollment in the same course. They want to reduce redundancy while preserving the ability to query all enrollments with student and course details. What is the most appropriate design approach?

Accepted Answer

Normalize the schema by creating separate Students, Courses, and Enrollments tables with foreign keys. Option C is correct because normalizing the schema into separate Students, Courses, and Enrollments tables eliminates data redundancy by storing each student's name and each course's name only once, while using foreign keys to maintain relationships. This preserves the ability to query all enrollments with student and course details via JOIN operations, which is the standard relational database design principle for reducing anomalies and storage overhead.

Answer

Keep the single table but use compression to reduce storage

Answer

Create a view that mirrors the single table but physically store data in separate normalized tables

Answer

Denormalize by adding more columns to the single table

Question 11

A retail company runs its legacy order management application on an on-premises SQL Server. They plan to migrate to Azure with minimal application changes and need high availability with automatic failover to a secondary Azure region. They also require full database-level isolation and the ability to use SQL Server Agent jobs. Which Azure deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance (C) is correct because it provides near-100% compatibility with on-premises SQL Server, including full database-level isolation (a dedicated instance) and full support for SQL Server Agent jobs. It also supports auto-failover groups for high availability with automatic failover to a secondary Azure region, meeting the migration requirement with minimal application changes.

Answer

Azure SQL Database single database

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machine

Question 12

A financial application uses Azure SQL Database. The workload consists of a high volume of small, frequent insert operations (OLTP) and periodic complex analytical queries that scan large portions of the same table (OLAP). The table currently has a clustered columnstore index. The inserts are suffering from performance degradation. What should the company do to improve insert performance while still enabling efficient analytical queries?

Accepted Answer

Replace the clustered columnstore index with a clustered rowstore index and add a nonclustered columnstore index. Option A is correct because a clustered rowstore index is optimized for high-volume OLTP inserts, while adding a nonclustered columnstore index allows the same table to support efficient analytical queries by providing a separate columnar structure. This hybrid approach avoids the insert overhead of columnstore indexes, which are designed for bulk operations and can suffer from small, frequent insert performance degradation due to delta store management and tuple mover processes.

Answer

Use memory-optimized tables for the entire table

Answer

Partition the table by date and move older partitions to columnstore

Answer

Keep the clustered columnstore index and use batch inserts

Question 13

A global e-commerce company uses Azure SQL Database for its order management system. They need to ensure high availability with the ability to fail over to an Azure region in a different continent in case of a regional outage. They also want to use the secondary database for read-intensive reporting without affecting the primary's performance. Which Azure SQL Database feature should they enable?

Accepted Answer

Active geo-replication. Active geo-replication is the correct choice because it creates readable secondary replicas of an Azure SQL Database in a different Azure region (including a different continent). It supports manual failover to the secondary region during an outage, and the secondary can be used for read-only query workloads like reporting without impacting the primary database's performance.

Answer

Long-term backup retention

Answer

Automatic tuning

Answer

Connection pooling

Question 14

A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows. Queries frequently aggregate data for the current month, but also need to retain historical data for 7 years. The company wants to improve query performance for the monthly aggregations and simplify data archiving. Which design should they implement?

Accepted Answer

Partition the table by month and create aligned indexes.. Partitioning the Transactions table by month allows SQL Server to perform partition elimination during queries that aggregate data for the current month, scanning only the relevant partition(s) instead of the entire table. Aligned indexes ensure that index structures follow the same partition scheme, maintaining efficiency for both queries and maintenance. This design also simplifies data archiving by enabling fast partition switching to move older months out of the table without costly delete operations.

Answer

Create a clustered columnstore index on the entire table.

Answer

Use Azure SQL Database elastic pool for the database.

Answer

Implement transparent data encryption.

Question 15

An e-commerce application uses Azure SQL Database. The Orders table stores millions of rows with columns: OrderID (primary key, clustered index), CustomerID, OrderDate, OrderStatus, TotalAmount. Queries frequently filter on OrderDate and OrderStatus, and sort results by OrderDate DESC. Which indexing strategy will most improve query performance for these filters and sort?

Accepted Answer

Create a nonclustered index on OrderDate, OrderStatus and include other columns needed by the query.. Option B is correct because a nonclustered index on (OrderDate, OrderStatus) supports both the filter and the sort in a single index seek/scan. SQL Server can use the index to locate rows matching both predicates and return them already sorted by OrderDate DESC without a separate sort operation, which is critical for performance on millions of rows.

Answer

Create a nonclustered index on OrderDate only.

Answer

Create a clustered columnstore index on the table.

Answer

Create a nonclustered index on OrderStatus only.