Difficulty: Easy
Correct Answer: Correct
Explanation:
Introduction / Context:
Indexes in SQL Server organize data to accelerate lookups, joins, sorts, and predicates, much like a book index. They can dramatically reduce I/O and CPU for read-heavy workloads and can also improve write performance in some patterns by supporting seek access and narrowing locks. Understanding their role is foundational in database tuning.
Given Data / Assumptions:
Concept / Approach:
Indexes provide ordered access paths and mapping from key values to data locations. They reduce scans by enabling seeks, support efficient JOIN/ORDER BY/GROUP BY, and help the optimizer choose better plans. Trade-offs include additional storage, maintenance overhead on data modification, and potential write amplification.
Step-by-Step Solution:
Verification / Alternative check:
Compare execution plans with and without indexes; use Query Store to confirm performance deltas; validate with realistic workloads.
Why Other Options Are Wrong:
Common Pitfalls:
Over-indexing; redundant indexes; missing covering columns; stale statistics; ignoring write overhead.
Final Answer:
Correct
Discussion & Comments