Core purpose of SQL Server indexes: Evaluate the statement: "SQL Server indexes are special data structures used to improve performance."

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:

• We refer to the general case: clustered and nonclustered B-tree indexes as primary structures.
• Specialized indexes (columnstore, XML, spatial, full-text) exist but share the performance goal.
• Appropriate statistics and cardinality estimation are available.

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:

• Incorrect: Contradicts the core design of indexing.
• SELECT-only: Indexes also help JOIN, GROUP BY, window functions, and sometimes constraints.
• SSD-only / row-count-only: Benefits are logical and apply regardless of media or table size (though magnitude varies).

Common Pitfalls: Over-indexing; redundant indexes; missing covering columns; stale statistics; ignoring write overhead.

Final Answer: Correct