A few days ago I wrote about Infobright’s column-based storage engine, and compared the sizes of raw text data file, gzipped file, MyISAM files, and Infobright files. At that time, I also wanted to compare that against data compression in SQL Server 2008, which is a new feature. But the Windows cloud server instance I fired up at the time didn’t have enough disk space, so I temporarily aborted that endeavour, until today.
Once again, testing data was generated using TPC-H’s dbgen tool. In fact I took the same steps outlined here. The total raw text file size is around 8.8 GB. I then created 2 SQL Server tables without any index (heap), one without compression and one with page compression. The DDL used is based on DDL listed in this post, without the indexes.
I used BULK INSERT for data loading. Here is the statement used:
BULK INSERT [testDb].[dbo].[LINEITEM] FROM 'C:\Users\Administrator\Documents\lineitem.tbl' WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char', FIELDTERMINATOR='|',ROWTERMINATOR='0x0a')
I then calculated storage space taken with sp_spaceused stored procedure.
Here is the results in GB:
Raw Text: 8.8
Raw Text After GZIP: 2.6
Uncompressed SQL Server table data size: 9.5
Compressed SQL Server table data size: 7.4
For clarity’s sake, here is the results with Infobright and MyISAM on Linux:
MyISAM File Size: 7.2
SQL Server 2008 provides a system stored procedure that gives a size estimation if compression is used for a non-compressed table/index.The estimated size of a compressed table is 5.4 GB. Comparing it with 7.4, the estimation appears to be optimistic, in this case.