Administering a SQL Database Infrastructure — Question 12

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a data warehouse that stored sales data. One fact table has 100 million rows.
You must reduce storage needs for the data warehouse.
You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.
Solution: You remove all clustered indexes, sort the transactions in the table, and create a clustered index on the table, so that the table is not a heap.
Does the solution meet the goal?

Answer options

Correct answer: A

Explanation

The solution is valid because creating a clustered index on sorted transactions optimizes storage and improves retrieval efficiency, meeting the goal of reducing storage needs. Removing clustered indexes initially allows for sorting, and the eventual clustered index ensures the table is organized, which is essential for real-time analytics. Option B is incorrect as the solution does meet the requirements stated.