CompTIA DataSys+ (DS0-001)Database FundamentalsHard
A data engineer is optimizing a large `Transactions` table with millions of records. Queries frequently filter by `TransactionDate` and then sort by `TransactionAmount`. To speed up these specific queries, which type of index would be most beneficial?
- AClustered Index
- BNon-Clustered Index on `TransactionAmount` only
- CNon-Clustered Index on `TransactionDate` and `TransactionAmount`
- DHash Index
Show answer & explanationAnswer & explanation
Correct answer: C. Non-Clustered Index on `TransactionDate` and `TransactionAmount`
For queries that filter by one column (`TransactionDate`) and then sort by another (`TransactionAmount`), a composite non-clustered index on both columns, in that order, is highly effective. The index can be used to efficiently find the filtered records and then provide them pre-sorted, avoiding a separate sort operation.
Why the other options are wrong
- A. A clustered index sorts the physical data, which is good for range queries and sorting, but a table can only have one. If a clustered index already exists on another column (e.g., `TransactionID`), a non-clustered index is needed for this specific query pattern.
- B. An index only on `TransactionAmount` would not help with the initial filtering by `TransactionDate` efficiently, and vice-versa.
- D. Hash indexes are good for equality lookups but not for range queries (like filtering by date range) or sorting.
Composite Non-Clustered Index
A non-clustered index created on multiple columns, optimized for queries that filter or sort by those columns in a specific order.
- The order of columns in the index is crucial for query optimization.
- Can be used to cover queries, meaning all needed data is in the index itself.
- Useful for queries with `WHERE` clauses on leading columns and `ORDER BY` on subsequent columns.
Memory trick: Composite Index: 'Combine' for 'Complex' queries.