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

Question 1

A retail company uses Azure SQL Database for its sales transaction table, which contains over 500 million rows. Queries that filter by OrderDate are slow because the database scans the entire table. The database administrator decides to implement table partitioning on the OrderDate column. What is the primary benefit of this partitioning strategy?

Accepted Answer

It improves query performance by enabling partition elimination, where only relevant partitions are scanned.. Table partitioning in Azure SQL Database divides a large table into smaller, manageable segments based on a partition key (here, OrderDate). The primary benefit is partition elimination: queries with filters on OrderDate can scan only the relevant partition(s) instead of the entire 500-million-row table, drastically reducing I/O and improving query performance.

Answer

It reduces the total storage required by compressing older partitions.

Answer

It enforces referential integrity between partitions automatically.

Answer

It eliminates the need for indexes on the partitioned column.

Question 2

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table with billions of rows. The table is distributed using hash distribution on the SaleDate column. Queries that join this fact table with a small dimension table (Product) on ProductID are slow because the join requires shuffling data across distributions. Which design change would most improve the performance of these join queries?

Accepted Answer

Replicate the Product dimension table to all distributions.. Replicating the Product dimension table to all distributions eliminates the need to shuffle data across distributions during the join. In Azure Synapse dedicated SQL pool, hash distribution distributes rows across 60 distributions based on the hash of the distribution column (SaleDate). When joining on ProductID, which is not the distribution column, data must be moved between distributions. Replicating the small dimension table ensures each distribution has a local copy, allowing the join to be performed without data movement, significantly improving performance.

Answer

Change the distribution of the fact table to round-robin.

Answer

Partition the fact table by SaleDate.

Answer

Create a nonclustered index on ProductID in the fact table.

Question 3

A startup has an application with unpredictable usage patterns on Azure SQL Database. They want to minimize cost by paying only for the compute they use and the database should automatically pause during idle periods. Which Azure SQL Database option should they choose?

Accepted Answer

Serverless. Azure SQL Database Serverless is designed for applications with unpredictable usage patterns, as it automatically scales compute resources based on demand and pauses the database during idle periods to eliminate compute costs. This model charges per second for the compute used, making it the most cost-effective choice for workloads that have periods of inactivity.

Answer

Provisioned (DTU or vCore)

Answer

Hyperscale

Answer

Business Critical

Question 4

A company runs a customer-facing application on an Azure SQL Database. The application experiences high read traffic from reporting queries that cause performance degradation on the primary database. The company needs a solution that offloads reporting queries without impacting the transactional workload, and the solution must keep the reporting data synchronized within seconds. Which feature should they use?

Accepted Answer

Active Geo-Replication. Active Geo-Replication creates a readable secondary replica of the Azure SQL Database in a different Azure region. This secondary replica can handle read-only reporting queries, offloading them from the primary database, and data is synchronized asynchronously with a typical lag of seconds, meeting the near-real-time synchronization requirement without impacting the transactional workload.

Answer

Auto-failover groups

Answer

Elastic Database Transactions

Answer

Database Copy

Question 5

A company uses Azure SQL Database for a sales application. They need to replicate the database to a secondary region for disaster recovery. The secondary should be readable for reporting purposes and data should be synchronized within seconds. Which feature should they use?

Accepted Answer

Active Geo-Replication. Active Geo-Replication is the correct choice because it creates a readable secondary replica in a different Azure region, with data synchronized within seconds via asynchronous replication. This meets the requirement for both disaster recovery and read-only reporting access, as the secondary can be queried directly without impacting the primary database.

Answer

Auto-failover groups

Answer

Point-in-time restore

Answer

Long-term retention

Question 6

A company uses Azure SQL Database to store customer order data. They need to automatically track changes to the 'OrderStatus' column in the 'Orders' table. They want to be able to query the current status and also easily retrieve historical status changes for a given order without writing custom triggers or history tables. Which feature should they enable?

Accepted Answer

Temporal tables. Temporal tables (system-versioned) automatically track full row history, including changes to the 'OrderStatus' column, by maintaining a paired history table. This allows querying both current and historical states with simple T-SQL clauses like FOR SYSTEM_TIME, without custom triggers or manual history tables.

Answer

Change Data Capture (CDC)

Answer

Automatic tuning

Answer

Geo-replication

Question 7

A company uses Azure SQL Database for an order processing system. The Orders table has columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. The Customers table has CustomerID (PK), Name, Email. The database administrator wants to ensure that when a customer record is deleted, all orders for that customer are also automatically deleted. Which database constraint should be implemented?

Accepted Answer

ON DELETE CASCADE on Orders.CustomerID. Option B is correct because ON DELETE CASCADE on the foreign key (Orders.CustomerID) automatically deletes all child rows in the Orders table when the parent row in the Customers table is deleted. This ensures referential integrity without requiring additional code or triggers, and is the standard SQL mechanism for cascading deletes in Azure SQL Database.

Answer

ON DELETE SET NULL on Orders.CustomerID

Answer

ON UPDATE CASCADE on Customers.CustomerID

Answer

A trigger on Customers table

Question 8

A company runs an e-commerce application on Azure SQL Database. During seasonal promotions, traffic spikes significantly, but at other times traffic is low. They want to automatically adjust compute resources based on demand without manual intervention or provisioning. Which Azure SQL Database feature should they use?

Accepted Answer

Serverless compute. Serverless compute for Azure SQL Database automatically scales compute resources based on workload demand and pauses the database during inactive periods, charging only for storage and compute used per second. This matches the requirement for automatic adjustment without manual intervention or provisioning, especially for intermittent, unpredictable traffic spikes like seasonal promotions.

Answer

Geo-replication

Answer

Elastic pools

Answer

Hyperscale

Question 9

A database administrator manages an Azure SQL Database with a table that has a clustered index on OrderID. Frequent queries filter on OrderDate and then sort the results by CustomerID. These queries perform poorly. Which indexing strategy will most improve performance for these specific queries?

Accepted Answer

Create a nonclustered index on (OrderDate, CustomerID). The query filters on OrderDate and then sorts by CustomerID. A nonclustered index on (OrderDate, CustomerID) supports both the WHERE clause (by OrderDate) and the ORDER BY clause (by CustomerID) as a covering index, allowing the database engine to perform a single index seek or scan without needing a separate sort operation. This directly addresses the performance bottleneck by eliminating the need to sort the results after filtering.

Answer

Create a nonclustered index on (CustomerID, OrderDate)

Answer

Change the clustered index to (OrderDate)

Answer

Create a filtered index on OrderDate WHERE CustomerID IS NOT NULL

Question 10

A company has an on-premises SQL Server 2016 database that uses SQL Server Agent jobs and cross-database queries across multiple databases. They want to migrate to Azure with minimal application changes, requiring native VNet integration and near 100% compatibility with the on-premises SQL Server instance. Which Azure deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and cross-database queries, while offering native VNet integration. This minimizes application changes during migration, unlike other Azure SQL options that lack these features.

Answer

Azure SQL Database single database

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 11

A company runs a web application on Azure SQL Database that experiences unpredictable spikes in traffic. They want to automatically adjust compute resources based on demand without manual intervention and without over-provisioning. Which Azure SQL Database feature should they use?

Accepted Answer

Serverless compute tier. The Serverless compute tier for Azure SQL Database automatically scales compute resources based on workload demand, pausing databases during idle periods and resuming them when traffic spikes occur. This eliminates the need for manual intervention and prevents over-provisioning by charging only for the compute used per second, making it ideal for unpredictable traffic patterns.

Answer

Active geo-replication

Answer

Hyperscale service tier

Answer

Read scale-out

Question 12

A company uses Azure SQL Database for an employee management system. The Employees table has 10 million rows and a clustered index on EmployeeID (the primary key). Queries that filter employees by Department and then sort by HireDate are very slow. Which indexing strategy will most improve performance for these queries?

Accepted Answer

Create a nonclustered index on (Department, HireDate) and include the other needed columns as included columns.. A nonclustered index on (Department, HireDate) with included columns is optimal because it supports both the WHERE clause filter on Department and the ORDER BY on HireDate as a covering index. The index key order matches the query's filter and sort requirements, allowing SQL Server to perform a single index seek and avoid key lookups by including all needed columns. This eliminates the need to scan the clustered index or sort rows after filtering.

Answer

Create a nonclustered index on (HireDate, Department) with no included columns.

Answer

Create a clustered index on Department.

Answer

Drop the existing clustered index and recreate a clustered columnstore index.

Question 13

A company runs an e-commerce application on Azure SQL Database. The application experiences heavy read traffic from reporting dashboards that query the same tables as the transactional workload. This causes performance degradation for the application. The company needs a solution that offloads reporting queries to a read-only copy that stays synchronized within minutes, without impacting transactional performance. Which Azure SQL Database feature should they use?

Accepted Answer

Active geo-replication. Active geo-replication creates a readable secondary replica of the Azure SQL Database that can be used for read-only workloads like reporting dashboards. The secondary replica stays synchronized with the primary database using asynchronous replication, typically within seconds to minutes, ensuring near-real-time data without impacting transactional performance on the primary.

Answer

Auto-failover groups

Answer

Elastic pools

Answer

Query Performance Insight

Question 14

A company runs an e-commerce application on Azure SQL Database. The application experiences unpredictable traffic spikes during flash sales and promotional events. The company wants to automatically scale compute resources based on actual demand and pay only for the resources consumed. Which Azure SQL Database deployment option best meets these requirements?

Accepted Answer

Serverless. The Serverless deployment option for Azure SQL Database automatically scales compute resources (vCores) based on actual demand, pausing the database during idle periods and resuming on the first connection. This model charges per second for the compute used, making it ideal for unpredictable traffic spikes like flash sales, as it eliminates the need to over-provision and ensures you pay only for consumed resources.

Answer

Provisioned DTU

Answer

Provisioned vCore

Answer

Hyperscale

Question 15

A financial application stores transactions in an Azure SQL Database table with columns: TransactionID (clustered index), AccountID, TransactionDate, Amount. Queries frequently filter on AccountID and TransactionDate together. The table contains millions of rows. Which index strategy will most improve query performance for these filters?

Accepted Answer

Nonclustered index on (AccountID, TransactionDate). Option D creates a nonclustered index on (AccountID, TransactionDate) that acts as a covering index for queries filtering on both columns. SQL Server can seek directly to the matching rows using the composite key order, avoiding a full table scan or key lookup. This is the most efficient strategy because the index is sorted by AccountID first, then TransactionDate, matching the query predicate exactly.

Answer

Clustered index on (AccountID, TransactionDate)

Answer

Nonclustered index on (TransactionDate)

Answer

Nonclustered index on (AccountID) INCLUDE (TransactionDate)