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

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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.

Question 10

A company uses Azure SQL Database to store order data. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status, TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate descending. Which indexing strategy will most improve query performance for these filters and sort?

Accepted Answer

Create a nonclustered index on (OrderDate DESC, Status) and include TotalAmount. Creates a covering nonclustered index on (OrderDate DESC, Status) that directly supports the frequent filter on OrderDate and Status and the ORDER BY OrderDate DESC. Including TotalAmount as a non-key column makes the index covering, so all needed columns come from the index without key lookups, maximizing query performance.

Answer

Create a clustered index on OrderDate

Answer

Create a nonclustered index on Status alone

Answer

Create a clustered columnstore index on the table

Question 11

A company runs an e-commerce platform on Azure SQL Database. The database handles many concurrent transactions (OLTP). The business team runs complex reporting queries on the same database during business hours, which slows down the transactional workload. The company wants to offload the reporting queries to a separate read-only copy of the database to avoid performance impact. Which Azure SQL Database feature should they enable?

Accepted Answer

Read scale-out. Read scale-out (C) is the correct feature because it allows Azure SQL Database to offload read-only workloads, such as complex reporting queries, to a separate read-only replica. This is achieved by using the `ApplicationIntent=ReadOnly` connection string parameter, which routes queries to a secondary replica, thereby preventing performance impact on the primary transactional (OLTP) workload. This feature is specifically designed for scenarios where you need to isolate reporting from high-concurrency OLTP operations without requiring a separate database copy.

Answer

Hyperscale service tier

Answer

Geo-replication

Answer

Elastic query

Question 12

A company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database has a large fact table that is partitioned by date (monthly partitions) to improve query performance and simplify data archiving. The company wants to maintain the same partitioning strategy in Azure to avoid rewriting queries. Which feature in Azure SQL Managed Instance should they use to achieve this?

Accepted Answer

Table partitioning with partition functions and schemes. Azure SQL Managed Instance supports table partitioning using partition functions and partition schemes, which is the same feature available in SQL Server. This allows you to define monthly partitions on the fact table using a date column, preserving the existing partitioning strategy and query logic without modification. The partition function maps rows to partitions based on the date boundary values, and the partition scheme assigns those partitions to filegroups.

Answer

Sharding across multiple Azure SQL Managed Instances

Answer

Index partitioning only

Answer

Federated tables

Question 13

A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries frequently filter on OrderDate and OrderStatus, and sort by OrderDate descending. Which indexing strategy will most improve query performance?

Accepted Answer

Create a non-clustered index on (OrderDate DESC, OrderStatus) and keep the existing clustered index on OrderID. Creates a covering index for the most common query pattern: filtering on OrderDate and OrderStatus, and sorting by OrderDate descending. By specifying DESC in the index key, the index is ordered in the same direction as the sort, allowing SQL Server to avoid a sort operation and retrieve rows in order directly from the index. This non-clustered index can satisfy the query entirely without touching the clustered index (OrderID), reducing I/O and improving performance.

Answer

Create a clustered index on OrderDate and a non-clustered index on OrderStatus

Answer

Create a non-clustered index on (OrderDate, OrderStatus) and keep the existing clustered index on OrderID

Answer

Create a clustered index on OrderID and a non-clustered index on (OrderStatus, OrderDate)

Question 14

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. The queries also return the Title and CopiesAvailable columns. Which indexing strategy will most improve query performance for these operations?

Accepted Answer

Create a nonclustered index on (Author, PublishedYear DESC) and include (Title, CopiesAvailable). It creates a covering nonclustered index on (Author, PublishedYear DESC) that directly supports the filter (Author) and sort (PublishedYear DESC) operations. Including Title and CopiesAvailable as non-key columns makes the index covering, meaning all required columns are in the index leaf level, so SQL Server can satisfy the query entirely from the index without key lookups to the clustered index. This minimizes I/O and improves query performance.

Answer

Create a nonclustered index on Author only

Answer

Create a nonclustered index on PublishedYear DESC

Answer

Keep only the existing clustered index on BookID

Question 15

A retail company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and OrderDate, and sort results by OrderDate in descending order. The queries also return the TotalAmount. Which indexing strategy will most improve query performance for these operations?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount).. It creates a covering nonclustered index that supports both the filter predicates (CustomerID and OrderDate) and the sort order (OrderDate DESC) while including TotalAmount as an included column to avoid key lookups. This index allows SQL Server to satisfy the query entirely from the index pages, minimizing I/O and improving performance.

Answer

Maintain the existing clustered index on OrderID only.

Answer

Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount).

Answer

Create a clustered columnstore index on the entire table.