Row-level compression works by using more efficient storage formats for fixed-length data.
Row-level compression uses the following strategies to save space:
1) Storing fixed-length numeric data types and CHAR data types as though they were variable-length data types.
2)Not storing NULL or 0 values.
3)Reducing metadata required to store data.
Although it does reduce the amount of space that data uses, row-level compression does not provide the storage improvements of page-level compression. The advantage of row level compression is that it requires less CPU usage than page-level compression. You use the following syntax to compress a table by using row-level compression:
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)
For example, to rebuild all partitions of the Sales.Customer table of the AdventureWorks2012 database by using row compression, utilize the following query:
ALTER TABLE [Sales].[Customer] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)
You use the following syntax to configure an index with row-level compression:
ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)