Developing SQL Databases — Question 112

You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB.
The table is updated thousands of times a day, but data is not deleted.
The SELECT statements vary in the number of columns used and the amount of rows retrieved.
You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.
Which indexing strategy should you use?

Answer options

• A. a nonclustered index for each column in the table
• B. a clustered columnstore index for the table
• C. a hash index for the table
• D. a clustered index for the table and nonclustered indexes for nonkey columns

Correct answer: B

Explanation

The correct choice is B, as a clustered columnstore index is designed specifically for large datasets and can significantly improve query performance for analytics and reporting scenarios. Options A and D may lead to slower performance due to multiple indices and increased overhead, while C, a hash index, is not suitable for the varied SELECT statements described.