SQL Server 2008 Enterprise Edition includes the ability to natively compress selected data on disk. This can provide significant space savings and improve performance of IO intensive workloads. When data compression is enabled, data pages are also compressed in the buffer pool (i.e., RAM). This provides the additional benefit of increasing the effective amount of application data that can be accessed from the data cache (faster) rather than disk (slower).
SQL Server 2008 supports both
: Row level compression takes fixed length data type columns and stores them in a variable length format.
: Page level compression goes even deeper than row level compression and minimizes the data redundnacy of the rows in a given page.
You should run the
system stored procedure or the
Data Compression Wizard
to evaluate how changing the compression state will affect a table or an index. Extra CPU overhead is required for the server to compress and decompress the data so it makes sense to get an estimate as to how much savings you will get for a given object.
Using the SQL Server 2008 AdventureWorks database we can look at and compare the two different types of compression (Row and Page) for an example object (Production.TransactionHistory)
EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW';
EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'PAGE';
sp_estimate_data_compression_savings takes a sample of the data from the table and compresses it in tempdb to determine the savings
If you decide to enabling compression on a table the syntax is as follows:
ALTER TABLE Production.TransactionHistory REBUILD WITH (DATA_COMPRESSION = PAGE)
This will NOT add data compression to the indexes on the table Production.TransactionHistory. A seperate ALTER INDEX statement is required for indexes if you decide to enable data compression.