Question
You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table.
| Name | Data type |
| --- | --- |
| SalesID | Int |
| Product | Int |
| Total Number | Numeric(8,4) |
| Tax Number | Numeric(8,4) |
| SalesRep | Varchar(30) |
FactSales has 6 billion rows and is loaded nightly by using a batch process. You must provide the greatest reduction in space for the database and maximize performance.
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 Correct
Explanation
Columnstore archival compression runs the Microsoft XPRESS algorithm on top of the columnstore compression that columnstore tables always use, so it further reduces the data size and gives the greatest space reduction. The question asks specifically which type saves the most space. Archival compression does cost extra time and CPU to store and retrieve data, which is why plain columnstore compression can be tempting, but plain columnstore compression saves less space. Row and page compression apply to rowstore tables and also save less on a large fact table like this one.
