DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database and wants to ensure that a specific query always uses a particular index. What should they do?
⚠ Common exam trap
Test-takers frequently confuse index maintenance (rebuilding, updating stats) with query plan control, thinking that a well-maintained index will automatically be used, when in fact the optimizer may still choose a different index based on cost estimates.
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
✓
Use a query hint to force the index
Query hints, specifically the INDEX hint, allow you to force the query optimizer to use a particular index for a specific query. In Azure SQL Database, this is done by adding `OPTION (TABLE HINT (table_name, INDEX (index_name)))` to the query, overriding the optimizer's default index selection.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Update statistics on the table
Why it's wrong here
Updating statistics refreshes the histogram and density data the optimizer uses for cardinality estimation, which can improve plan quality, but it does not instruct the optimizer to use any particular index. After an update, the cost-based optimizer remains free to choose a different index, a scan, or an alternative join strategy based on revised estimates. Therefore, it cannot guarantee that the desired index is forced.
- ✓
Use a query hint to force the index
Why this is correct
Using a query hint such as WITH (INDEX(index_name)) or OPTION (TABLE HINT(...)) explicitly instructs the Azure SQL Database query optimizer to use that index for the specific query, effectively overriding the cost-based decision. This is the only listed technique that directly forces the index at execution time. Apply it sparingly because it overrides the optimizer and may become stale as data distributions change.
- ✗
Rebuild the index
Why it's wrong here
Rebuilding an index reorganizes the B-tree structure, removes fragmentation, and refreshes statistics as a side effect, but the optimizer still makes a cost-based choice about whether to use it. A rebuilt index may become more attractive and efficient, yet the optimizer can ignore it and choose another path if it estimates a lower cost. Consequently, rebuilding improves index health, not index usage.
- ✗
Enable Query Store
Why it's wrong here
Enabling Query Store does not alter the optimizer's choice of index; it only captures query text, execution plans, and runtime statistics for monitoring and troubleshooting. While Query Store supports forcing a previously captured plan via the sys.query_store_plan_forcing catalog view, simply enabling it has no effect on index selection. An index hint or explicit plan forcing must be applied, which makes this option incorrect.
Go deeper
Related to this question
Learn chapter
Azure SQL Services
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
Index
An index is a data structure that speeds up data retrieval operations on a database table or file, much like a book index helps you find topics quickly.
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.