CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A database administrator is optimizing a large `Orders` table. They notice that queries frequently join `Orders` with `Customers` on `CustomerID` to retrieve customer details. To improve the performance of these join operations, which type of index would be most beneficial to create on the `CustomerID` column in the `Orders` table?
- ANon-Clustered Index
- BSpatial Index
- CFull-Text Index
- DClustered Index
Show answer & explanationAnswer & explanation
Correct answer: A. Non-Clustered Index
A non-clustered index is ideal for improving join performance on foreign key columns like `CustomerID` without altering the physical order of the `Orders` table's data, which might already be optimized for other common queries.
Why the other options are wrong
- B. A spatial index is used for optimizing queries on geographic or geometric data, which is not applicable here.
- C. A full-text index is used for searching text within columns, not for optimizing joins on ID columns.
- D. A clustered index defines the physical order of data in the table, which might not be desirable if the table is already ordered for another primary key or frequent query pattern.
Non-Clustered Index
A non-clustered index is a special type of index that creates a separate, sorted structure containing key values and pointers to the actual data rows in the table. It does not alter the physical order of the data.
- Stores data in one logical order and index in another physical order.
- Faster for lookups and joins on columns that are not the primary key.
- A table can have multiple non-clustered indexes.
Memory trick: Indexes guide your data search, not sort your books.