Which type of compression provides the greatest space reduction for the database?

You have an Azure SQL database that contains a table named factSales.

FactSales contains the columns shown in the following table.

FactSales has 6 billion rows and is loaded nightly by using a batch process.

Which type of compression provides the greatest space reduction for the database?
A . page compression
B . row compression
C . columnstore compression
D . columnstore archival compression

View Answer

Answer: D

Explanation:

Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. NOTE: Columnstore ― The columnstore index is also logically organized as a table with rows and columns, but the data is physically stored in a column-wise data format.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression