DP-900 Practice Question: Identify considerations for relational data on Azure
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?
⚠ Common exam trap
It's easy for candidates to choose a clustered index on the primary key by default, failing to recognize that the query predicate (LastLoginDate) is not the clustering key, so the index cannot be used to efficiently filter the data.
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
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Clustered index on CustomerID
Why it's wrong here
The clustered index on CustomerID already exists—typically created by the primary key constraint—and physically orders all rows by CustomerID. Because the filter condition uses LastLoginDate, this index's key order cannot help narrow the search; the database engine has to scan every data page to evaluate the date predicate. This is effectively a full table scan, so the existing clustered index offers no performance benefit for date-based filtering and is not a valid choice to speed up the query.
When this WOULD be correct
If the query frequently searched for a specific CustomerID (e.g., WHERE CustomerID = 123) or joined on CustomerID, a clustered index on CustomerID would be optimal for fast point lookups and range scans on the primary key.
- ✓
Non-clustered index on LastLoginDate
Why this is correct
A non-clustered index on LastLoginDate creates a separate B-tree structure ordered by that column, allowing SQL Server to perform an index seek directly for the date-range predicate. For a query such as WHERE LastLoginDate BETWEEN '2023-01-01' AND '2023-12-31', the engine navigates the index to the starting date and reads only the matching index entries, then uses bookmark lookups to fetch the full customer rows. This drastically reduces I/O compared to scanning every row in the table, making it the ideal choice for this filtering pattern.
- ✗
Non-clustered index on FullName
Why it's wrong here
An index on FullName does not help this query because FullName is not part of the WHERE clause filter. Indexes accelerate searching only by letting the optimizer seek on the indexed key, and since the predicate targets LastLoginDate, the FullName index would simply be ignored. Even if FullName appeared in the SELECT list, that alone wouldn't isolate the matching rows—it could only provide marginal benefit as a covering index, and none for filtering on date.
When this WOULD be correct
If the query frequently filters or searches by FullName (e.g., WHERE FullName = 'John Doe') or sorts by FullName, a non-clustered index on FullName would be correct to speed up those operations.
- ✗
Columnstore index on SignUpDate and LastLoginDate
Why it's wrong here
A columnstore index on SignUpDate and LastLoginDate is optimized for analytical workloads that scan large volumes of data and perform aggregations, not for transactional range lookups that return a small subset of rows. For a point or short-range predicate like a single LastLoginDate query, the columnstore engine must scan through compressed column segments, which is less efficient than a B-tree seek. It would consume storage and maintenance overhead without improving this row-based retrieval, making it an inappropriate solution for an OLTP-style customer management system.
When this WOULD be correct
A columnstore index on SignUpDate and LastLoginDate would be correct if the query involved aggregations (e.g., COUNT, SUM) over many rows, such as 'Find the average number of days between SignUpDate and LastLoginDate for all customers'.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓Non-clustered index on LastLoginDateCorrect answer▾
Why this is correct
A non-clustered index on LastLoginDate creates a separate B-tree structure ordered by that column, allowing SQL Server to perform an index seek directly for the date-range predicate. For a query such as WHERE LastLoginDate BETWEEN '2023-01-01' AND '2023-12-31', the engine navigates the index to the starting date and reads only the matching index entries, then uses bookmark lookups to fetch the full customer rows. This drastically reduces I/O compared to scanning every row in the table, making it the ideal choice for this filtering pattern.
✗Clustered index on CustomerIDWrong answer — click to see why▾
Why this is wrong here
A clustered index on CustomerID sorts the table by CustomerID, but the query filters on LastLoginDate. Without an index on LastLoginDate, the query must scan all 10 million rows, which is inefficient.
★ When this WOULD be the correct answer
If the query frequently searched for a specific CustomerID (e.g., WHERE CustomerID = 123) or joined on CustomerID, a clustered index on CustomerID would be optimal for fast point lookups and range scans on the primary key.
Why candidates choose this
Candidates often assume the primary key should always be the clustered index, but they overlook that the query's filter column (LastLoginDate) is more critical for this workload.
✗Non-clustered index on FullNameWrong answer — click to see why▾
Why this is wrong here
The query filters on LastLoginDate, not FullName. A non-clustered index on FullName would not help the query because it does not include the filter column, so the query would still require a full table scan.
★ When this WOULD be the correct answer
If the query frequently filters or searches by FullName (e.g., WHERE FullName = 'John Doe') or sorts by FullName, a non-clustered index on FullName would be correct to speed up those operations.
Why candidates choose this
Candidates may think any non-clustered index on any column will improve query performance, overlooking that the index must match the filter column used in the WHERE clause.
✗Columnstore index on SignUpDate and LastLoginDateWrong answer — click to see why▾
Why this is wrong here
A columnstore index is optimized for large-scale analytical queries (e.g., aggregations over many rows), not for point lookups or range scans on a single column like LastLoginDate. The query filters on a single date column, which is better served by a non-clustered index.
★ When this WOULD be the correct answer
A columnstore index on SignUpDate and LastLoginDate would be correct if the query involved aggregations (e.g., COUNT, SUM) over many rows, such as 'Find the average number of days between SignUpDate and LastLoginDate for all customers'.
Why candidates choose this
Candidates may think columnstore indexes are always faster for large tables (10 million rows) and that including multiple columns in the index is beneficial, not realizing that columnstore indexes are designed for data warehousing workloads, not transactional point queries.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Azure SQL Services
Key term
Row
A row is a horizontal record in a database table that contains all the information about a single entity, like one customer or one product.
Key term
Primary key
A primary key is a unique identifier for each record in a database table, ensuring that no two rows have the same value in that column.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 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-900 exam.