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:
- 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.
- 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).
- It stores fixed character strings by using variable-length format by not storing the blank characters.
- 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?
No comments:
Post a Comment