CompTIA DataSys+ (DS0-001)Database FundamentalsHard
A data analyst is working with a `Sales` table that contains `SaleID`, `ProductID`, `CustomerID`, `SaleDate`, and `SaleAmount`. The analyst frequently needs to retrieve `SaleID` and `SaleAmount` for specific `SaleDate` ranges. To optimize these queries, which type of index would be most beneficial if `SaleDate` is the primary column for filtering and sorting, and `SaleID` and `SaleAmount` are frequently retrieved alongside it?
- ANon-Clustered Index on `CustomerID`
- BUnique Index on `ProductID`
- CClustered Index on `SaleID`
- DCovering Index on (`SaleDate`, `SaleID`, `SaleAmount`)
Show answer & explanationAnswer & explanation
Correct answer: D. Covering Index on (`SaleDate`, `SaleID`, `SaleAmount`)
A covering index (also known as an included index or index with all columns) includes all the columns required by a query, meaning the database can retrieve all necessary data directly from the index without accessing the base table. This significantly improves performance for queries that only need indexed columns.
Why the other options are wrong
- A. A non-clustered index on `CustomerID` would help queries filtering by `CustomerID` but is irrelevant to queries based on `SaleDate`.
- B. A unique index on `ProductID` ensures uniqueness for products but doesn't optimize queries filtering by `SaleDate` or retrieving `SaleAmount`.
- C. A clustered index on `SaleID` would order the physical data by `SaleID` but wouldn't specifically optimize queries filtering on `SaleDate` and retrieving all three columns without table lookup.
Covering Index
A non-clustered index that includes all the columns required by a query, allowing the query to be satisfied entirely from the index without visiting the base table.
- Optimizes query performance by avoiding table lookups.
- Must contain all columns in the SELECT list, WHERE clause, and ORDER BY clause.
- Can increase index size and write overhead.
Memory trick: Indexes are shortcuts, covering indexes are express lanes.