DP-300 Covering Index Practice Question
You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?
⚠ Common exam trap
DP-300 often tests the distinction between covering indexes (INCLUDE columns) and other performance features like columnstore or partitioning; candidates incorrectly assume partitioning or columnstore will fix key lookup issues when the real fix is making the nonclustered index covering.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Add all queried columns as included columns to the nonclustered index
The nonclustered index on customer ID is being used, but the many key lookups indicate that the query needs columns not present in the index, forcing a lookup back to the clustered index for each row. Adding the queried columns as included columns to the nonclustered index makes it a covering index, eliminating the key lookups while adding only the necessary columns to the index leaf level — a much smaller storage footprint than a columnstore or partitioning solution.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create a clustered columnstore index on the table
Why it's wrong here
Columnstore is column-oriented and built for large scans and aggregations, not selective customer-ID point lookups; it cannot serve the existing nonclustered index's seek pattern, so key lookups persist. It is tempting because it compresses well and speeds analytical workloads, and would be correct for a reporting or data-warehouse table queried by range rather than by customer ID.
- ✗
Create a filtered index on customer ID for frequent values
Why it's wrong here
A filtered index covers only the frequent customer-ID values, so queries for other customers still seek the nonclustered index and perform key lookups, leaving the IO problem largely unsolved. It is tempting because it is small and cheap, and would be correct when a small, stable subset of values dominates query volume.
- ✗
Partition the table by customer ID
Why it's wrong here
Partitioning by customer ID splits data across partitions but does not eliminate the key lookups: the nonclustered index still seeks, then fetches remaining columns from the clustered index row by row. It is tempting for manageability and partition elimination on huge tables, and would be correct for sliding-window archival or per-tenant maintenance, not for removing lookups.
- ✓
Add all queried columns as included columns to the nonclustered index
Why this is correct
Included columns are stored at the nonclustered index leaf level, eliminating the key lookups back to the clustered index for the queried columns. This addresses the high IO and long execution times while adding far less storage than a new covering index.
Go deeper
Related to this question
Learn chapter
Optimizing Database Query and Index Performance
Key term
Azure SQL Indexes
Structures in Azure SQL Database that speed up data retrieval by providing quick access paths to rows, similar to a book index.
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
About these practice questions
Courseiva writes every DP-300 question from scratch — 574 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.