Home » Microsoft » 70-462 v.2 » What should you do?
You have a large partitioned fact table in a data warehouse. The table is stored as a clustered index.
You need to modify the indexing solution to minimize the amount of disk space required to store the table.
What should you do?
A. Enable row compression for the clustered index.
B. Enable page compression for the clustered index.
C. Implement a nonclustered columnstore index.
D. Implement a clustered columnstore index.
Correct Answer: D
Explanation/Reference:
Explanation:
Use a clustered columnstore index for large data warehouse tables.
The clustered columnstore index is more than an index, it is the primary table storage. It achieves high data compression and a significant improvement in query performance on large data warehousing fact and dimension tables. Clustered columnstore indexes are best suited for analytics queries rather than transactional queries, since analytics queries tend to perform operations on large ranges of values rather than looking up specific values.
Incorrect:
Not C: A nonclustered columnstore index and a clustered columnstore index function the same. The difference is a nonclustered index is a secondary index created on a rowstore table, whereas a clustered columnstore index is the primary storage for the entire table.
The nonclustered index contains a copy of part or all of the rows and columns in the underlying table. The index is defined as one or more columns of the table, and has an optional condition that filters the rows.
A nonclustered columnstore index enables real-time operational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the columnstore index.
References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance
