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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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.. 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 3

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

A: Create a nonclustered index on (DepartmentID, LastName) INCLUDE (Salary). 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 4

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. 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 5

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. 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 6

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 7

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 8

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 9

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.. 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.

Question 10

A company runs an online booking system on Azure SQL Database. The system handles many concurrent transactions (OLTP). The business team runs complex reporting queries on the same database during business hours, which slows down the booking transactions. The company needs a solution to separate the analytical workload from the transactional workload without duplicating data manually. Which Azure SQL Database feature should they use?

Accepted Answer

Read Scale-out (readable secondary replica). Read Scale-out (readable secondary replica) is the correct choice because it allows the company to offload complex reporting queries to a read-only replica of the primary database, thereby isolating the analytical workload from the OLTP transactions. This feature is built into Azure SQL Database and does not require manual data duplication or ETL processes, directly addressing the requirement to separate workloads without manual effort.

Answer

Active Geo-Replication

Answer

Elastic pools

Answer

Hyperscale service tier

Question 11

A library management system uses Azure SQL Database. The Books table has 500,000 rows with columns: BookID (primary key, clustered), Title, Author, ISBN, PublishedYear, CopiesAvailable. Queries frequently filter by Author and then sort results by PublishedYear in descending order. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on (Author, PublishedYear DESC) INCLUDE (CopiesAvailable, Title, ISBN).. It creates a covering index that matches the exact query pattern: filtering by Author and sorting by PublishedYear in descending order. By defining (Author, PublishedYear DESC) as the index key, SQL Server can perform an index seek on Author and an ordered scan on PublishedYear without a separate sort operation. Including the remaining columns (CopiesAvailable, Title, ISBN) as non-key columns makes the index covering, eliminating the need for key lookups to the clustered index.

Answer

Create a nonclustered index on (Author) INCLUDE (PublishedYear, CopiesAvailable, Title).

Answer

Create a nonclustered index on (PublishedYear) INCLUDE (Author).

Answer

Create a nonclustered columnstore index on (Author, PublishedYear, CopiesAvailable, Title, ISBN).

Question 12

A company runs individual Azure SQL Databases for each of its departments. The databases experience varying usage patterns; sometimes one database is idle while another is heavily loaded. The company wants to pool resources to reduce cost while ensuring each database gets resources when needed. Which Azure feature should they use?

Accepted Answer

Azure SQL Database elastic pool. Azure SQL Database elastic pools are designed to share resources (eDTUs or eVCores) across multiple databases with varying usage patterns. This allows idle databases to contribute their unused capacity to heavily loaded ones, reducing overall cost while ensuring each database gets resources when needed.

Answer

Azure SQL Database single database with provisioned DTUs

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Question 13

A company's application uses Microsoft SQL Server with multiple databases that need to run complex queries joining tables across databases. They are migrating to Azure and need a fully managed relational database service with high availability, automated backups, and minimal management overhead. They do not need a separate SQL Server installation and want to avoid managing VMs. Which Azure deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries and linked servers, while being a fully managed platform-as-a-service (PaaS) offering. It eliminates the need to manage VMs or a separate SQL Server installation, and it includes built-in high availability (99.99% SLA) and automated backups, meeting all stated requirements.

Answer

Azure SQL Database single database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database elastic pool

Question 14

A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows with a TransactionDate column. Queries frequently aggregate sales totals for the current month, but historical data must be retained for 7 years. Currently, queries scan the entire table, causing performance issues. The company also wants to simplify archiving of old data. Which design should they implement?

Accepted Answer

Implement table partitioning by month on TransactionDate.. Table partitioning by month on TransactionDate allows Azure SQL Database to efficiently manage and query large tables by splitting data into manageable segments. Queries that filter on TransactionDate for the current month will only scan the relevant partition(s), eliminating full table scans. Additionally, partitioning simplifies archiving by enabling swift partition switching to move old data to archive tables without complex ETL processes.

Answer

Create a non-clustered index on the TransactionDate column.

Answer

Create a materialized view for the current month's data.

Answer

Convert the table to use a clustered columnstore index.

Question 15

A retail application uses Azure SQL Database. The Products table contains 200,000 rows with columns: ProductID (primary key, clustered), CategoryID, ProductName, Price, StockQuantity. Queries frequently filter on CategoryID and then sort results by Price in descending order. Which indexing strategy will most improve query performance for these operations?

Accepted Answer

Create a nonclustered index on (CategoryID, Price) with Price in descending order.. Creates a composite nonclustered index on (CategoryID, Price DESC) that directly supports both the filter (CategoryID equality) and the sort (Price descending) in a single index seek and ordered scan, eliminating the need for a separate sort operation. This is the most efficient strategy because the index is ordered exactly as the query requires, allowing SQL Server to retrieve matching rows in the correct order without additional processing.

Answer

Create a clustered index on CategoryID.

Answer

Create a nonclustered index on CategoryID that includes Price as an included column.

Answer

Create a clustered columnstore index on the table.