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

Question 1

A company is migrating a SQL Server database to Azure SQL Database. The database uses CLR (Common Language Runtime) integration for business logic and has database mail configured. The company needs full instance-level functionality while still benefiting from the platform-as-a-service model. Which Azure SQL 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 is the correct choice because it provides near 100% compatibility with SQL Server on-premises, including support for CLR integration and Database Mail, while still offering a platform-as-a-service (PaaS) model. Single databases and elastic pools lack these instance-scoped features, and Hyperscale is a scaling option for single databases, not a separate deployment type that adds instance-level functionality.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database Hyperscale

Question 2

A company has 15 on-premises SQL Server databases, each 20–40 GB, running on a single instance. They rely on cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance. They want to migrate to Azure with minimal application changes and reduce administrative overhead. Which Azure SQL 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 cross-database queries using three-part names and SQL Server Agent. This allows the company to migrate with minimal application changes while offloading administrative overhead like patching and backups, which are handled by Azure.

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Answer

SQL Server on Azure Virtual Machines

Question 3

A company has an Azure SQL Database with an 'Orders' table containing millions of rows. The table has a clustered index on OrderID (primary key). Queries frequently filter by CustomerID (equality) and OrderDate (range). These queries are slow and cause high logical reads. Which index strategy will most improve performance for these specific queries?

Accepted Answer

Create a non-clustered index on (CustomerID, OrderDate).. A non-clustered index on (CustomerID, OrderDate) is a covering index for queries filtering by CustomerID (equality) and OrderDate (range). It allows SQL Server to perform an index seek on CustomerID, then a range scan on OrderDate, retrieving all needed columns without touching the clustered index (if the query is covered). This dramatically reduces logical reads compared to a full clustered index scan or a key lookup.

Answer

Rebuild the clustered index on (OrderDate, CustomerID).

Answer

Create a non-clustered index on OrderDate.

Answer

Create a filtered index on OrderDate for recent dates.

Question 4

A software company develops a multi-tenant SaaS application. They deploy a separate Azure SQL Database for each tenant. The databases are small (2-5 GB) and have highly variable loads — some tenants use the app heavily during the day, others at night. The company wants to maximize resource utilization and minimize costs by allowing databases to share a pool of resources, while still maintaining a predictable performance per database. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. C is correct because an elastic pool allows multiple Azure SQL databases with variable and unpredictable usage patterns to share a fixed pool of resources (eDTUs or vCores), maximizing resource utilization and minimizing cost. The pool provides a predictable performance per database through per-database min/max resource limits, which is ideal for the described multi-tenant SaaS scenario with small databases and highly variable loads.

Answer

Single database with DTU purchasing model

Answer

Single database with vCore purchasing model

Answer

Azure SQL Managed Instance

Question 5

A retail company uses Azure SQL Database to store a large fact table of sales transactions with millions of rows. They run complex aggregate queries (SUM, COUNT, AVG) across many rows for monthly reports. These queries take too long. Which index type should they add to the table to improve performance?

Accepted Answer

Clustered columnstore index. Clustered columnstore indexes are optimized for large fact tables and analytical workloads because they store data in a columnar format, which significantly reduces the amount of data read from disk for aggregate queries like SUM, COUNT, and AVG. This index type also uses batch processing and compression to accelerate query performance on millions of rows, making it ideal for monthly reporting queries.

Answer

Clustered B-tree index

Answer

Nonclustered rowstore index

Answer

Nonclustered columnstore index

Question 6

A company plans to migrate a 500 GB SQL Server database from on-premises to Azure SQL Database. They require minimal downtime during the migration. Which approach should they use?

Accepted Answer

Use Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct approach because it supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database using the transactional replication technology. This allows the source database to remain operational during the migration, and only a brief cutover is needed at the end to switch applications to the target.

Answer

Export a BACPAC file and import to Azure SQL Database

Answer

Use Azure Site Recovery to replicate the database

Answer

Create a full database backup and restore to Azure SQL Database

Question 7

A hospital uses Azure SQL Database to store patient appointment records. The 'Appointments' table has columns: AppointmentID (primary key), PatientID, DoctorID, AppointmentDate, and Status. Queries frequently filter by DoctorID (equality) and AppointmentDate (range) to retrieve a doctor's schedule. Currently, these queries are slow. Which index strategy will most improve performance for these queries?

Accepted Answer

Add a nonclustered index on (DoctorID, AppointmentDate).. A nonclustered index on (DoctorID, AppointmentDate) supports both equality filtering on DoctorID and range filtering on AppointmentDate. This index structure allows SQL Server to perform a single index seek for the doctor, then a range scan within that doctor's appointments, avoiding a full table scan. The order of columns matters: the leading column (DoctorID) handles the equality predicate, and the second column (AppointmentDate) handles the range predicate efficiently.

Answer

Add a clustered index on AppointmentID.

Answer

Add a columnstore index on the Status column.

Answer

Add a nonclustered index on (AppointmentDate, DoctorID).

Question 8

A company uses Azure SQL Database for an e-commerce platform. The 'Orders' table has millions of rows with columns OrderID (primary key), CustomerID, OrderDate, and TotalAmount. Queries often filter by CustomerID (equality) and OrderDate (range). Currently, these queries are slow. Which index should be created to improve performance?

Accepted Answer

A nonclustered index on (CustomerID, OrderDate). The query pattern filters by CustomerID (equality) and OrderDate (range). A composite nonclustered index on (CustomerID, OrderDate) allows SQL Database to seek directly to the matching CustomerID rows and then efficiently scan the ordered OrderDate range within that partition, avoiding a full table scan or key lookup. This index order leverages the index's B-tree structure for both equality and range predicates.

Answer

A nonclustered index on OrderID

Answer

A nonclustered index on OrderDate

Answer

A clustered index on CustomerID

Question 9

A company has an on-premises SQL Server database with a 1 TB 'Sales' table containing historical data. They want to move this table to Azure SQL Database with minimal downtime. The table is actively written to during business hours. Which approach should they use?

Accepted Answer

Use Azure Data Migration Service with continuous sync from on-premises to Azure SQL Database, then cut over. Azure Data Migration Service (DMS) with continuous sync is the correct approach because it supports online migration with minimal downtime. It uses transactional replication to keep the on-premises SQL Server database synchronized with Azure SQL Database while the source remains fully operational, allowing a controlled cutover with only seconds of downtime.

Answer

Use the Azure SQL Database migration wizard to perform an offline migration over the weekend

Answer

Export the table as a .bacpac file and import it into Azure SQL Database during off-hours

Answer

Use SQL Server Management Studio's 'Deploy Database to Azure SQL Database' wizard

Question 10

A SaaS company hosts a multi-tenant application. Each tenant has a separate Azure SQL Database. The databases are small (1-3 GB) and their workloads vary significantly over time, with some tenants active during business hours and others at night. The company wants to maximize resource utilization and minimize costs by pooling compute resources across tenants while maintaining predictable performance per database. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Azure SQL Database Elastic Pool. Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases (tenants) to share a fixed pool of compute and storage resources, enabling cost efficiency through resource pooling while providing predictable performance via per-database resource limits (min/max DTU or vCore). This matches the scenario of small databases with variable, non-overlapping workloads across tenants.

Answer

Azure SQL Database Single Database

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 11

A logistics company uses Azure SQL Database to store millions of shipment records. The table has columns: ShipmentID (primary key), CustomerID, ShipDate, and Destination. Queries frequently filter by CustomerID and ShipDate to retrieve shipments for a specific customer over a date range. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on CustomerID and ShipDate. A nonclustered index on CustomerID and ShipDate is the best choice because it directly supports the frequent query pattern filtering by both columns. This composite index allows SQL Database to perform an index seek rather than a full table scan, drastically reducing I/O for selective queries over millions of rows.

Answer

Create a clustered index on ShipmentID

Answer

Partition the table by ShipmentID

Answer

Create a full-text index on Destination

Question 12

A company wants to migrate an on-premises SQL Server database to Azure. The database uses SQL Agent jobs to run nightly ETL processes and relies on Service Broker for asynchronous messaging between applications. They want to minimize changes to the application and database code. Which Azure SQL 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 SQL Agent jobs and Service Broker. This allows the company to migrate the database with minimal code changes, as these features are not available in Azure SQL Database (single or elastic pool). SQL Server on Azure VMs would also support these features but requires more management overhead and is not a fully managed PaaS option.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 13

A company uses Azure SQL Database to store a large table of sales transactions with columns: TransactionID (primary key), CustomerID, ProductID, SaleDate, Amount. Queries frequently filter by both CustomerID and SaleDate to retrieve sales for a specific customer over a date range. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on (CustomerID, SaleDate). Creating a nonclustered index on (CustomerID, SaleDate) as a composite index directly supports the query predicate that filters by both CustomerID and SaleDate. The index is ordered by CustomerID first, enabling efficient seeks for a specific customer, and then by SaleDate within each customer, allowing the query engine to perform a range scan for the date range without scanning the entire table or sorting. This index is a covering index for this query, as it contains all columns needed for the filter, avoiding key lookups.

Answer

Create a clustered index on SaleDate

Answer

Create a nonclustered index on CustomerID and include SaleDate

Answer

Create a nonclustered index on (SaleDate, CustomerID)

Question 14

A company maintains a large 'Transactions' table in Azure SQL Database. The table has a clustered index on a GUID column (TransactionID). Over time, they observe slow insert performance due to index fragmentation and page splits. They also need fast point lookups by TransactionID. Which approach should they take to improve insert performance while still supporting fast lookups?

Accepted Answer

Change the clustered key to an integer IDENTITY column and keep a nonclustered index on TransactionID. Using an integer IDENTITY column as the clustered key eliminates the random insertion order and page splits caused by a GUID clustered index, while the nonclustered index on TransactionID provides fast point lookups. In Azure SQL Database, a clustered index determines the physical order of data; a monotonically increasing integer avoids fragmentation and improves insert throughput.

Answer

Change the clustered index to a nonclustered index on TransactionID and make the table a heap

Answer

Keep the clustered index on TransactionID but rebuild it daily

Answer

Remove the clustered index entirely and create a nonclustered index on TransactionID

Question 15

A global e-commerce company uses Azure SQL Database for its product catalog. The application experiences high read traffic for product detail pages, often running the same queries for popular items. The database’s write workload is moderate. The company wants to improve read performance without increasing the cost of the primary database tier and without changing the application code. Which Azure SQL Database feature should they implement?

Accepted Answer

Read scale-out. Read scale-out (A) is correct because it offloads read-only queries to a secondary replica of the Azure SQL Database without changing the application code. By setting `ApplicationIntent=ReadOnly` in the connection string, the database routes read queries to a read-only replica, improving performance for high-read workloads like product detail pages while keeping the primary tier unchanged and avoiding additional cost for a higher tier.

Answer

Active geo-replication

Answer

Azure SQL Database elastic pool

Answer

Query Performance Insight