DP-900 Practice Question: Identify considerations for relational data on Azure
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?
⚠ Common exam trap
A common mix-up: candidates assume rebuilding the clustered index (Option C) is a sufficient maintenance fix, but the DP-900 exam tests understanding that the root cause is the choice of clustered key data type, not just fragmentation management.
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
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Change the clustered index to a nonclustered index on TransactionID and make the table a heap
Why it's wrong here
While this avoids GUID fragmentation, heaps can lead to performance degradation for queries that need to scan or sort data. Also, point lookups via a nonclustered index on a heap require extra key lookups (RID lookups), which are less efficient than clustered index seeks.
When this WOULD be correct
In a scenario where the table is used primarily for bulk inserts and queries are full table scans or range scans with no need for fast point lookups, and the GUID column is never used for range queries or ordering, then a heap with a nonclustered index on GUID could be acceptable.
- ✓
Change the clustered key to an integer IDENTITY column and keep a nonclustered index on TransactionID
Why this is correct
An integer IDENTITY column provides sequential values that reduce fragmentation and page splits, improving insert performance. The nonclustered index on TransactionID supports efficient point lookups. This is a recommended pattern when the natural key is not ideal for clustering.
- ✗
Keep the clustered index on TransactionID but rebuild it daily
Why it's wrong here
Rebuilding the clustered index daily only cleans up fragmentation after it has already accumulated; it does nothing to prevent the page splits that occur continuously as new rows with random GUID TransactionID values are inserted between existing keys. Throughout the day, those inserts still trigger costly page splits and index maintenance, so query performance degrades until the next rebuild. Moreover, a daily rebuild on a large table consumes significant I/O, CPU, and log space, and it can lock the table or require ONLINE rebuild with extra overhead, making it a band-aid that treats the symptom rather than the root cause.
When this WOULD be correct
This option would be correct in a scenario where the table has a clustered index on an integer column that experiences moderate fragmentation over time, and the workload is read-heavy with periodic batch inserts that can tolerate a maintenance window for index rebuilds to restore performance.
- ✗
Remove the clustered index entirely and create a nonclustered index on TransactionID
Why it's wrong here
This makes the table a heap, which can cause performance issues for range queries and other operations. Sequential insert performance may improve slightly, but point lookups require a nonclustered index followed by a RID lookup, which is slower than a clustered index seek. Also, heaps can suffer from high fragmentation over time.
When this WOULD be correct
In a scenario where the table is used primarily for bulk inserts and point lookups are rare or not required, and the main goal is to minimize insert overhead without concern for lookup speed, making the table a heap with a nonclustered index on the lookup column could be acceptable.
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.
✓Change the clustered key to an integer IDENTITY column and keep a nonclustered index on TransactionIDCorrect answer▾
Why this is correct
An integer IDENTITY column provides sequential values that reduce fragmentation and page splits, improving insert performance. The nonclustered index on TransactionID supports efficient point lookups. This is a recommended pattern when the natural key is not ideal for clustering.
✗Change the clustered index to a nonclustered index on TransactionID and make the table a heapWrong answer — click to see why▾
Why this is wrong here
Making the table a heap (no clustered index) eliminates page splits from GUID inserts, but point lookups by TransactionID require a nonclustered index, which still suffers from fragmentation and includes a costly key lookup (RID) to the heap, degrading lookup performance.
★ When this WOULD be the correct answer
In a scenario where the table is used primarily for bulk inserts and queries are full table scans or range scans with no need for fast point lookups, and the GUID column is never used for range queries or ordering, then a heap with a nonclustered index on GUID could be acceptable.
Why candidates choose this
Candidates know that GUIDs cause fragmentation and page splits in clustered indexes, so they think removing the clustered index entirely (heap) will solve insert performance, but they overlook the impact on point lookup performance and the need for a key lookup.
✗Keep the clustered index on TransactionID but rebuild it dailyWrong answer — click to see why▾
Why this is wrong here
Rebuilding the clustered index daily does not address the root cause of fragmentation and page splits caused by GUID-based clustered keys; inserts will continue to cause fragmentation between rebuilds, leading to ongoing performance degradation.
★ When this WOULD be the correct answer
This option would be correct in a scenario where the table has a clustered index on an integer column that experiences moderate fragmentation over time, and the workload is read-heavy with periodic batch inserts that can tolerate a maintenance window for index rebuilds to restore performance.
Why candidates choose this
Candidates may think that regular index maintenance (rebuilding) is a universal solution for fragmentation, without understanding that the fundamental issue is the GUID clustering key causing excessive page splits, which cannot be fully mitigated by periodic rebuilds.
✗Remove the clustered index entirely and create a nonclustered index on TransactionIDWrong answer — click to see why▾
Why this is wrong here
Removing the clustered index entirely and creating only a nonclustered index on TransactionID would make the table a heap, which eliminates page splits but significantly degrades point lookup performance because lookups would require a key lookup (RID) into the heap, adding extra I/O.
★ When this WOULD be the correct answer
In a scenario where the table is used primarily for bulk inserts and point lookups are rare or not required, and the main goal is to minimize insert overhead without concern for lookup speed, making the table a heap with a nonclustered index on the lookup column could be acceptable.
Why candidates choose this
Candidates may think that removing the clustered index eliminates fragmentation and page splits entirely, and that a nonclustered index alone can still support fast lookups, underestimating the performance penalty of key lookups in a heap.
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
Data Roles and Core Concepts
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
Key term
Column
A column is a vertical set of values in a database table that stores one specific type of attribute for every row.
About these practice questions
This DP-900 question is part of Courseiva's 820-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. 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.