Row Compression in SQL Server

SQL Server supports row compression on tables, indexes and partitions inside a database to reduce the size of the database which improve performance of I/O intensive workloads. Row compression will consume additional space when it is implemented which is known as metadata overhead.

Enabling compression only changes the physical storage format of the data that is associated with a data type but not its syntax or semantics. Application changes are not required when one or more tables are enabled for compression. The new record storage format has the following main changes:

1. It reduces the metadata overhead that is associated with the record. This metadata contains information about columns, their lengths and offsets. In some cases, the metadata overhead might be larger than the old storage format.
2. It uses variable-length storage format for numeric types (for example integer, decimal, and float) and the types that are based on numeric (for example datetime and money).
3. It stores fixed character strings by using variable-length format by not storing the blank characters.
4. NULL and 0 values across all data types are optimized and take no bytes.

How does Row Compression work?

To understand this functionality, we are created a table called RowCompression having two columns, one column would be numeric and another one would be string type. We are going to insert 100000 records in this table and will measure the occupied space also as given below:

------ Create table: RowCompression CREATE TABLE RowCompression   (      Id INT,                  DataValue VARCHAR(50)    ) ----- Declare local variable to incremental with default value DECLARE @ictr INT =1 ----- Insert Value into the RowCompression WHILE @ictr <= 100000   BEGIN   ----- Insert value and casting for int to varchar       INSERT INTO RowCompression (Id, DataValue)       VALUES (@ictr, 'RowCompression for:'+ Cast(@ictr as Varchar(10)))   ----- set incremental here       SET @ictr = @ictr + 1   END   ---- after load values check the space of the table SP_SpaceUsed RowCompression

In the above table, you can see that it’s using 4296KB as reserved and 4248 KB for data size. To compress the data we are going to Row compression on this table as given below:

---- Alter TABLE with data compression technique ---- by using data_compression = ROW ALTER TABLE dbo.RowCompression REBUILD WITH (data_compression = ROW)   ---- after applying data compression technique on table   ---- check occupied space again SP_SpaceUsed RowCompression

You can see that after applying row compression on the table, reserved and data size have been decreased accordingly 3656KB and 3632KB.

Conclusion

Row compression will delete the space that is not used in fixed sized strings, which means that a CHAR(10) can become a CHAR(1) if only 1 character is in it. Trailing padding characters are removed.  Remember it is ROW compression so it will determine the actual used space per value not per column over the entire table/index. Unfortunately Dynamics Ax doesn’t use fixed sized string (it uses NVARCHAR instead), so we cannot gain anything by this.
To know more on-
Considerations for When You Use Row and Page Compression
ColumnStore Archive Compression in SQL Server
How does Row Compression work?
What is Data Compression in SQL Server?
What is Page Compression in SQL Server?