DP-900 Practice Test 18 — 15 Questions

Question 1

A company stores user-submitted profile photos. Each photo is accessed frequently for the first month after upload, then accessed rarely for the next year. After one year, the photos are deleted. Which Azure Blob Storage access tier should be used for the first month to minimize cost while ensuring low-latency access?

Accepted Answer

Hot tier. The Hot tier is correct because it provides low-latency access and is optimized for frequent read/write operations, which matches the requirement of frequent access during the first month. It minimizes cost compared to Premium (which is for high-throughput scenarios) while still offering the necessary performance for user-submitted profile photos.

Answer

Cool tier

Answer

Archive tier

Answer

Premium tier

Question 2

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 3

A company stores IoT temperature readings in Azure Cosmos DB using the NoSQL API. Each document contains: DeviceID, Timestamp, Temperature, Location. Data is ingested at a rate of 10,000 documents per second from thousands of devices. The most common query is 'Get all readings for a specific DeviceID in the last hour.' Which partition key should be chosen to avoid hot partitions while still supporting the query efficiently?

Accepted Answer

A synthetic key combining DeviceID and Timestamp (e.g., DeviceID_yyyy-MM-dd-HH). Option D is correct because a synthetic key combining DeviceID and Timestamp (e.g., DeviceID_yyyy-MM-dd-HH) ensures that data for a specific device is distributed across multiple physical partitions based on the hour, preventing a single partition from becoming a hot spot. This design still supports the most common query efficiently by allowing Cosmos DB to route the query to only the partitions containing the relevant hour's data, using the partition key in the WHERE clause.

Answer

DeviceID

Answer

Timestamp (e.g., per minute)

Answer

Location

Question 4

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 5

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 6

A company stores IoT sensor data as JSON files in Azure Blob Storage. A data analyst needs to run ad-hoc SQL queries on these files without moving the data and without provisioning any compute clusters. The analyst wants to pay only for the amount of data processed by each query. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool allows you to query data directly from Azure Blob Storage using T-SQL without provisioning any compute clusters. It uses a pay-per-query model where you are billed only for the amount of data processed, making it ideal for ad-hoc SQL queries on JSON files stored in Blob Storage without data movement.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Data Factory

Question 7

A company stores JSON documents for a product catalog. Each document has a flexible schema because different product categories have different attributes. The catalog is read-heavy and requires low-latency lookups by product ID. The company expects to handle millions of products and needs to serve customers globally with low latency. Which Azure NoSQL data store should they choose?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model NoSQL database that natively supports JSON documents with flexible schemas, provides single-digit-millisecond latency for read-heavy workloads via automatic indexing, and offers turnkey global distribution across Azure regions to serve customers worldwide with low latency.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 8

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. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on (Author, PublishedYear DESC) INCLUDE (CopiesAvailable, Title, ISBN).. Option C is correct because it creates a covering index that matches the exact query pattern: filtering by Author and sorting by PublishedYear in descending order. By defining (Author, PublishedYear DESC) as the index key, SQL Server can perform an index seek on Author and an ordered scan on PublishedYear without a separate sort operation. Including the remaining columns (CopiesAvailable, Title, ISBN) as non-key columns makes the index covering, eliminating the need for key lookups to the clustered index.

Answer

Create a nonclustered index on (Author) INCLUDE (PublishedYear, CopiesAvailable, Title).

Answer

Create a nonclustered index on (PublishedYear) INCLUDE (Author).

Answer

Create a nonclustered columnstore index on (Author, PublishedYear, CopiesAvailable, Title, ISBN).

Question 9

A company uses Azure SQL Database for a financial application. Regulatory compliance requires that database backups be retained for 7 years. The current configuration uses the default point-in-time restore (PITR) retention of 7 days. Which Azure SQL Database feature should the company enable to meet the 7-year retention requirement?

Accepted Answer

Long-term retention (LTR) for backups. Azure SQL Database's default point-in-time restore (PITR) retains backups for only 7 days, which is insufficient for the 7-year regulatory requirement. Long-term retention (LTR) allows you to retain full database backups for up to 10 years by configuring backup policies in the Azure portal or via T-SQL, meeting the compliance need.

Answer

Active geo-replication

Answer

Auto-failover groups

Answer

Geo-redundant backup storage

Question 10

A company uses Azure SQL Database for its order management system. The database has a table named Orders with columns OrderID (INT, PRIMARY KEY), CustomerID (INT), OrderDate (DATE), TotalAmount (DECIMAL). Queries that filter by OrderDate are slow. The database administrator observes that the nonclustered index on OrderDate has high fragmentation and many page splits. Which action will most likely improve query performance for these date-based queries?

Accepted Answer

Rebuild the nonclustered index on OrderDate with a FILLFACTOR of 80.. Rebuilding the nonclustered index on OrderDate with a FILLFACTOR of 80 reduces page splits by leaving free space in each leaf-level page. This accommodates future insertions and updates that modify the OrderDate values, lowering fragmentation and improving query performance for date-based filters.

Answer

Change the data type of TotalAmount from DECIMAL to FLOAT.

Answer

Remove the clustered index on OrderID and create a clustered index on OrderDate.

Answer

Add a columnstore index on the OrderDate column.

Question 11

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 12

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 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). Option C is correct because 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 uses Azure SQL Database for an order management system. The 'Orders' table has millions of rows and is queried frequently with filters on OrderDate and CustomerID. The table currently has a clustered index on OrderID. Which action will most improve query performance for these frequent filters?

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 15

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