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

Topic 5, Misc. Questions

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.

Incorrect Answers:

B: Rowstore ― The rowstore index is the traditional style that has been around since the initial release of SQL Server.

For rowstore tables and indexes, use the data compression feature to help reduce the size of the database.

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