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

Question 1

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?

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

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 2

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

Question 3

A company uses Azure SQL Database for a reporting application. The database is mostly idle during weekdays but experiences heavy load on weekends when reports are generated. They want to minimize costs by only paying for compute resources when the database is active. Which Azure SQL Database pricing model should they choose?

Accepted Answer

Serverless. The Serverless pricing model for Azure SQL Database automatically pauses the database during periods of inactivity (e.g., weekdays) and resumes it when load increases (e.g., weekends), charging only for compute resources consumed during active periods. This aligns perfectly with the described workload pattern of mostly idle weekdays and heavy weekend usage, minimizing costs by eliminating charges for idle compute.

Answer

Provisioned DTU

Answer

Provisioned vCore

Answer

Hyperscale

Question 4

A company uses Azure SQL Database for an e-commerce application. The Orders table contains columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). Queries frequently filter by both CustomerID and OrderDate to retrieve orders for a specific customer within a date range. Which indexing strategy will most improve the performance of these queries?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate).. A nonclustered index on (CustomerID, OrderDate) directly supports the query filter that uses both columns. The index is ordered by CustomerID first, enabling SQL Server to quickly locate all rows for a specific customer, and then within that customer, the OrderDate column is ordered to efficiently scan the date range. This index covers the query's WHERE clause without needing to scan the entire table.

Answer

Create a clustered index on OrderID.

Answer

Create a nonclustered index on (OrderDate, CustomerID).

Answer

Create a nonclustered index on TotalAmount.

Question 5

A company runs an e-commerce application on Azure SQL Database. The database experiences high transaction volume during business hours (9 AM to 6 PM) but very low activity at night and on weekends. They want to optimize costs by paying only for the compute resources used, while ensuring the database can automatically scale up during peak periods and scale down (or pause) during idle times. Which Azure SQL Database purchasing model and compute tier should they choose?

Accepted Answer

vCore-based purchasing model with serverless compute tier. The vCore-based purchasing model with serverless compute tier is correct because it automatically scales compute resources based on workload demand and can pause during idle periods, charging only for consumed compute and storage. This matches the requirement of high transaction volume during business hours and low activity at night/weekends, optimizing costs by eliminating charges for unused compute capacity.

Answer

DTU-based purchasing model

Answer

vCore-based purchasing model with provisioned compute tier

Answer

vCore-based purchasing model with Hyperscale service tier

Question 6

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent to run scheduled jobs and performs cross-database queries within the same instance. The company wants a fully managed PaaS solution that requires minimal application changes. 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 full SQL Server Agent support for scheduled jobs and enables cross-database queries within the same instance, while being a fully managed PaaS service. It offers near 100% compatibility with on-premises SQL Server, minimizing application changes during migration.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 7

A SaaS company manages hundreds of customer databases, each representing a tenant. Each tenant database has its own predictable usage pattern, but the aggregate workload across all tenants is variable. The company wants to optimize costs by pooling compute resources across tenants while still ensuring that each tenant benefits from resource isolation under normal loads. Which Azure SQL Database deployment model should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed for SaaS multi-tenant scenarios where each tenant has a predictable, low average usage but the aggregate workload across tenants is variable. They allow pooling of compute resources (eDTUs or vCores) across multiple databases, providing resource isolation under normal loads via per-database min/max resource limits, while optimizing cost by sharing unused capacity among tenants.

Answer

Single database

Answer

Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 8

A company has a table named 'Sales' in Azure SQL Database with columns: SaleID (int, primary key), ProductID (int), SaleDate (datetime), Quantity (int), UnitPrice (decimal), TotalAmount (computed column). Queries frequently run to retrieve the total Quantity and UnitPrice for a specific ProductID over a date range. The query filters on ProductID and SaleDate and selects only Quantity and UnitPrice. Which index would most improve query performance?

Accepted Answer

Nonclustered index on (ProductID, SaleDate) INCLUDE (Quantity, UnitPrice). It creates a covering nonclustered index that supports both the WHERE clause (ProductID, SaleDate) and the SELECT clause (Quantity, UnitPrice) without needing to access the base table. The index key order matches the query filter, and the included columns avoid key lookups, minimizing I/O for the frequent aggregation queries.

Answer

Nonclustered index on (SaleDate) INCLUDE (Quantity, UnitPrice)

Answer

Clustered index on (ProductID, SaleDate)

Answer

Nonclustered index on (ProductID) INCLUDE (Quantity, UnitPrice)

Question 9

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent for nightly maintenance jobs, Service Broker for asynchronous messaging, and requires cross-database queries within the same instance. The company wants a fully managed Platform as a Service (PaaS) solution that minimizes application code changes. 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 SQL Server Agent, Service Broker, and cross-database queries within the same instance, while being a fully managed PaaS offering. This minimizes application code changes because the migration can be performed with minimal schema or code modifications, unlike Azure SQL Database which lacks these features.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database Hyperscale

Question 10

A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), TotalAmount (decimal). Queries frequently filter on CustomerID and OrderDate to find orders from a specific customer within a date range. Which index would most improve performance for these queries?

Accepted Answer

A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount). The query filters on CustomerID and OrderDate, so a composite non-clustered index on (CustomerID, OrderDate) allows SQL Server to perform an index seek on both columns, drastically reducing the number of rows scanned. Including TotalAmount as a non-key column makes this a covering index, meaning all needed data (including TotalAmount) is in the index leaf pages, avoiding costly key lookups to the clustered index.

Answer

A clustered index on OrderID

Answer

A non-clustered index on Status

Answer

A non-clustered index on (OrderDate, TotalAmount)

Question 11

A company uses Azure SQL Database for a customer management system. The Customers table has columns: CustomerID (int, primary key), FullName (varchar(100)), Email (varchar(200)), SignUpDate (date), LastLoginDate (date). Queries frequently filter on LastLoginDate to find customers who have not logged in for over a year for a promotional campaign. The table has 10 million rows. Which type of index should they create to optimize these queries?

Accepted Answer

Non-clustered index on LastLoginDate. A non-clustered index on LastLoginDate allows the query to quickly locate rows where LastLoginDate is older than one year without scanning the entire 10-million-row table. Azure SQL Database uses B-tree structures for non-clustered indexes, enabling efficient range scans and key lookups for the filtered rows. This directly supports the promotional campaign query pattern.

Answer

Clustered index on CustomerID

Answer

Non-clustered index on FullName

Answer

Columnstore index on SignUpDate and LastLoginDate

Question 12

A financial application requires strict consistency and transaction support (ACID). Which Azure data service is most appropriate for storing its core transactional data?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service that provides full ACID (Atomicity, Consistency, Isolation, Durability) transaction support through its SQL Server engine. It is the correct choice for a financial application requiring strict consistency and transactional integrity, as it guarantees that all transactions are processed reliably and adhere to the ACID properties.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure Data Lake Storage

Question 13

A database designer wants to reduce data redundancy and improve data integrity by splitting a large table into multiple related tables based on functional dependencies. This process is known as:

Accepted Answer

Normalization. Normalization is the process of organizing a relational database into multiple related tables to reduce data redundancy and improve data integrity by eliminating functional dependencies that cause anomalies. This is a core concept in relational database design, directly aligning with the scenario described in the question.

Answer

Denormalization

Answer

Partitioning

Answer

Indexing

Question 14

A company operates a high-volume order processing system on Azure SQL Database. During peak hours, many concurrent transactions try to insert and update rows in the same table, causing contention on page latches. Indexing and query optimization are already tuned. Which feature should the company implement to reduce write contention while preserving ACID properties?

Accepted Answer

In-Memory OLTP. In-Memory OLTP is correct because it uses memory-optimized tables and natively compiled stored procedures to reduce latch contention by eliminating the need for page latches entirely. Transactions operate directly on in-memory data structures, using optimistic multi-version concurrency control (MVCC) to detect conflicts without blocking, which preserves ACID properties while allowing high concurrency.

Answer

Read Scale-out

Answer

Elastic Database Query

Answer

Transparent Data Encryption (TDE)

Question 15

A development team is designing a relational database for a hospital patient management system. They need to ensure that each patient's medical record number is unique and not null. Which database constraint should they use?

Accepted Answer

PRIMARY KEY. The PRIMARY KEY constraint enforces both uniqueness and non-nullability on the column(s) it is applied to. In a relational database, the medical record number is the natural candidate for the primary key of the Patient table because it uniquely identifies each patient and must always have a value. This directly meets the requirement that each patient's medical record number is unique and not null.

Answer

FOREIGN KEY

Answer

CHECK

Answer

UNIQUE