A table inventory has a clustered index on product_id and a non-clustered index on location. You run this update:

hard📝 Application Q9 of 15

SQL - Indexes and Query Performance

A table inventory has a clustered index on product_id and a non-clustered index on location. You run this update:

UPDATE inventory SET location = 'Warehouse B' WHERE product_id = 100;

Which indexes are affected and how?

ABoth indexes are updated, slowing the update significantly.

BOnly the non-clustered index on <code>location</code> is updated, slowing the update.

COnly the clustered index on <code>product_id</code> is updated.

DNo indexes are updated because <code>product_id</code> is in the WHERE clause.

Step-by-Step Solution

Solution:

Step 1: Identify columns being updated and indexed
The location column is updated and has a non-clustered index; product_id is in the WHERE clause and is clustered index key.
Step 2: Understand index update rules
Updating a column with an index requires updating that index. The clustered index is not updated because product_id is not changed.
Final Answer:
Only the non-clustered index on location is updated, slowing the update. -> Option B
Quick Check:
Update indexed column = update its index only [OK]

Quick Trick: Only indexes on updated columns are changed [OK]

Common Mistakes:

Master "Indexes and Query Performance" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes