Database Reference
In-Depth Information
■
You can explore page compression in a delta store by recreating the
dbo.CCI
table and inserting a batch of rows,
which benefit from page compression after the clustered columnstore index is created. The simple way to do this is by
populating
Col2
with repetitive strings using the
REPLICATE('a',100)
function. You will notice the compression
information record if you examine the delta store data page with the
DBCC PAGE
command afterwards.
Tip
Archival Compression
Archival compression
is another feature introduced in SQL Server 2014. It can be applied on a table or on an individual
partition by specifying a
DATA_COMPRESSION=COLUMNSTORE_ARCHIVE
columnstore index property, and it reduces
storage space even further. It uses the Xpress 8 compression library, which is an internal Microsoft implementation of
the LZ77 algorithm. This compression works directly with binary data without any knowledge of the underlying SQL
Server data structures.
Archival compression works transparently with other SQL Server features. Columnstore data is compressed at the
time it is saved on disk and decompressed before it is loaded into memory.
Let's compare the results of different compression methods. I created four different tables with the same
schema, as shown in Listing 35-5. The first two tables are heaps with no nonclustered indexes defined. The first table
was uncompressed and the second one was compressed with page compression. The third and fourth tables had
clustered columnstore indexes compressed with
COLUMNSTORE
and
COLUMNSTORE_ARCHIVE
compression methods
respectively. Each table had almost 62 million rows generated based on the
dbo.FactResellerSales
table from the
AdventureWorksDW2012
database.
Listing 35-5.
Schema of test tables
create table dbo.FactSalesBig
(
ProductKey int not null,
OrderDateKey int not null,
DueDateKey int not null,
ShipDateKey int not null,
CustomerKey int not null,
PromotionKey int not null,
CurrencyKey int not null,
SalesTerritoryKey int not null,
SalesOrderNumber nvarchar(20) not null,
SalesOrderLineNumber tinyint not null,
RevisionNumber tinyint not null,
OrderQuantity smallint not null,
UnitPrice money not null,
ExtendedAmount money not null,
UnitPriceDiscountPct float not null,
DiscountAmount float not null,
ProductStandardCost money not null,
TotalProductCost money not null,
SalesAmount money not null,
TaxAmt money not null,
Freight money not null,
