70-463 · Question #233
You are designing a data warehouse that uses SQL Server 2012. The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four…
The correct answer is C. Non-clustered. The factSales table already has a clustered index on its primary key, so adding a non-clustered index is the appropriate way to improve query retrieval speed for analytical queries.
Question
You are designing a data warehouse that uses SQL Server 2012. The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the INT data type, and the aggregate column uses the MONEY data type. You need to increase the speed of data retrieval from the factSales table. Which index type should you add to the table?
Options
- AFull text
- BSpatial
- CNon-clustered
- DClustered
How the community answered
(37 responses)- A8% (3)
- B3% (1)
- C76% (28)
- D14% (5)
Why each option
The factSales table already has a clustered index on its primary key, so adding a non-clustered index is the appropriate way to improve query retrieval speed for analytical queries.
A Full-text index is designed for text search operations on character-based columns and is not applicable to tables storing numeric keys and money aggregates.
A Spatial index is designed for geometry or geography data types and has no relevance to a fact table containing INT and MONEY columns.
A non-clustered index can be added to the factSales table to provide fast lookup paths on foreign key or aggregate columns used in reporting queries, and since the table already has a clustered index, a non-clustered index is the only viable additional index type for improving read performance.
A table can only have one clustered index, and factSales already has one on its primary key, so adding another clustered index is not possible.
Concept tested: Selecting appropriate index type for data warehouse fact table
Source: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes
Topics
Community Discussion
No community discussion yet for this question.