[Solved] Given a table products with a non-clustered index on category_id, what happens internally when you run this query?UPDATE products SET price = price * 1.1 WHERE category_id = 5; | SQL

SQL - Indexes and Query Performance

Given a table products with a non-clustered index on category_id, what happens internally when you run this query?

UPDATE products SET price = price * 1.1 WHERE category_id = 5;

AThe update ignores the index and scans the whole table.

BThe update is slower because the index on <code>category_id</code> must be rebuilt entirely.

CThe update fails because indexes cannot be used with UPDATE statements.

D

The update is faster because the index helps find rows quickly, but the index must be updated if <code>category_id</code> changes.

Step-by-Step Solution

Solution:

Step 1: Understand index use in WHERE clause
The index on category_id helps quickly find rows where category_id = 5.
Step 2: Consider index updates during UPDATE
Since category_id is not changed, the index entries remain valid; only the data column price is updated.
Final Answer:
The update is faster because the index helps find rows quickly, but the index must be updated if category_id changes. -> Option D
Quick Check:
Index speeds search; update affects data, not index keys [OK]

Quick Trick: Indexes speed WHERE search; update index only if key changes [OK]

Common Mistakes:

Master "Indexes and Query Performance" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Given a table products with a non-clustered index on category_id, what happens internally when you run this query?