A data analyst is working with a `Sales` table that contains `SaleID`, `ProductID`, `CustomerID`, `SaleDate`, and `Amount`. The analyst frequently needs to retrieve `SaleID`, `SaleDate`, and `Amount` for sales made after a specific date, ordered by `SaleDate`. To optimize this specific query without impacting other queries on the `Sales` table, which type of index would be most beneficial?
- AA full-text index on `SaleDate`
- BA clustered index on `SaleDate`
- CA unique index on `SaleID`
- DA non-clustered index on `SaleDate` including `SaleID` and `Amount`
Show answer & explanationAnswer & explanation
Correct answer: D. A non-clustered index on `SaleDate` including `SaleID` and `Amount`
A non-clustered index on `SaleDate` would efficiently filter by date. Including `SaleID` and `Amount` as 'included columns' (or a covering index) means the query can be fully satisfied directly from the index without accessing the base table, which significantly boosts performance for this specific query. A clustered index changes the physical order of data, potentially impacting other queries.
Why the other options are wrong
- A. A full-text index is used for keyword searching within text columns, not for date filtering or numerical value retrieval.
- B. A clustered index on `SaleDate` would optimize range queries on date but reorders the physical data, potentially negatively impacting other queries that need different ordering or access patterns.
- C. A unique index on `SaleID` helps with primary key lookups but doesn't optimize filtering by `SaleDate` or retrieving other columns efficiently for this specific query.
Covering Index (Non-Clustered with Included Columns)
A non-clustered index that includes all columns referenced in a query (SELECT, WHERE, ORDER BY, GROUP BY clauses), allowing the database to satisfy the query entirely from the index without accessing the base table.
- Significantly speeds up specific queries.
- Reduces disk I/O by avoiding table lookups.
- Does not affect the physical storage order of the data.
- Can be larger than simple non-clustered indexes due to included columns.
Memory trick: COVERING indexes 'cover' all your query needs, no table trips!