Which type of index should you add to provide the fastest query times?

You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table.

The table contains 50 columns and 5 billion rows and is a heap.

Most queries against the table aggregate values from approximately 100 million rows and return only two columns.

You discover that the queries against the fact table are very slow.

Which type of index should you add to provide the fastest query times?
A . nonclustered columnstore
B. clustered columnstore
C. nonclustered
D. clustered

View Answer

Answer: B

Explanation:

Clustered columnstore indexes are one of the most efficient ways you can store your data in dedicated SQL pool.

Columnstore tables won’t benefit a query unless the table has more than 60 million rows.

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool