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

Question 1

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?

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

Accepted Answer

ON DELETE CASCADE on Orders.CustomerID. 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 2

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 3

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 4

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 5

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?

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

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 6

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 7

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

Question 8

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?

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

Accepted Answer

Create a non-clustered index on OrderDate and CustomerID. The frequent filters on OrderDate and CustomerID require a covering index that includes both columns. A non-clustered index on (OrderDate, CustomerID) allows SQL Server to perform an index seek for queries filtering on those columns, avoiding full clustered index scans on the existing clustered index on OrderID. This directly reduces I/O and improves query response times.

Answer

Create a clustered index on OrderDate

Answer

Create a non-clustered index on OrderID

Answer

Partition the table by CustomerID

Question 9

A company has multiple independent databases for different business units, each with low to moderate usage and varying workload patterns. They want to consolidate these databases into a single Azure SQL Database deployment option to share resources and reduce costs, while ensuring that databases do not starve each other of resources. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed for exactly this scenario: multiple databases with low to moderate usage and varying workload patterns. They allow databases to share a fixed pool of resources (eDTUs or vCores) while using built-in resource governance to prevent any single database from starving others, thus optimizing cost and performance.

Answer

Single database (DTU model)

Answer

Managed Instance

Answer

Database per server (hyperscale)

Question 10

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?

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

Accepted Answer

C. Create a nonclustered index on (CustomerID, OrderDate).. Creating a nonclustered index on (CustomerID, OrderDate) directly supports the query's filter (WHERE CustomerID = ?) and sort (ORDER BY OrderDate) operations. This composite index allows SQL Server to seek on CustomerID and then retrieve rows in OrderDate order without a separate sort, eliminating the need for a full clustered index scan on OrderID. It is a covering index for this query pattern, significantly reducing I/O and CPU overhead.

Answer

A. Create a nonclustered index on OrderDate only.

Answer

B. Create a filtered index on CustomerID where Status = 'Active'.

Answer

D. Create a nonclustered index on OrderID and OrderDate.

Question 11

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?

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

Accepted Answer

Create a nonclustered composite index on (OrderDate, CustomerID). The query filters on both OrderDate and CustomerID, so a composite nonclustered index on (OrderDate, CustomerID) allows SQL Server to perform a single index seek to locate the matching rows without touching the clustered index until the final key lookup. This dramatically reduces I/O compared to scanning the entire clustered index or using multiple separate indexes.

Answer

Create a nonclustered index on OrderID only

Answer

Create separate nonclustered indexes on OrderDate and CustomerID

Answer

Create a clustered index on CustomerID instead of OrderID

Question 12

A company needs to store order data for an e-commerce platform. The system requires high concurrency, fast inserts, and the ability to enforce referential integrity between tables (e.g., Customers and Orders). Which Azure service should they use?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service that supports high concurrency, fast inserts, and enforces referential integrity through foreign key constraints. It provides ACID transactions and row-level locking to handle concurrent writes efficiently, making it ideal for e-commerce order processing where data consistency between Customers and Orders tables is critical.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Answer

Azure Data Lake Storage Gen2

Question 13

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 14

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?

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 15

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