SQL Server 2008 row data and page data compression decryption

The performance of SQL Server depends primarily on disk I/O efficiency, and improving I/O efficiency some program means improved performance. SQL Server 2008 provides data compression capabilities to improve disk I/O.

Data compression means reducing the disk footprint of the data, so data compression can be used in tables, clustered indexes, nonclustered indexes, view indexes, or partitioned tables, partitioned indexes.

Data compression can be implemented at two levels: row level and Page level. Page-level compression includes row-level compression. Tables and indexes can be compressed at the same time they are created, or you can use ALTER TABLE Rebuild with or ALTER index Rebuild with syntax to compress existing tables or indexes. When the compression state on the heap changes, all nonclustered indexes are rebuilt.

Let's look at what the compression process is doing.

Row compression:

• 1. Reduce metadata header record data.
• 2. All numeric types (integer,decimal,float) or types based on numeric types are compressed (Datetime,money). For example, 100 is present in an INT field, accounting for 4 bits, but only 1 bits are required from 0~255, and after compression, 3byte space is saved.
• Both 3.Char and nchar are stored in variable-length types. The reason is ditto. For example, CSDN is present in char (10), but it requires only char (4) space, so it releases char (6) space after compression.
• 4. All types of NULL and 0 do not account for bytes.

Page compression:

• 1. Do row compression.
• 2. Pre-Standard compression: All columns on each page, below the row header, each row is stored with a row definition value, and after compression, all row definition values are replaced by a reference to the header value.
• 3. Dictionary compression: Dictionary compression is similar to pre-label compression. In pre-standard compression, a value that differs from other normal values is defined on each column. However, in dictionary compression, the normal value of all columns in each page is preceded by the next line of the wardrobe. These values are then replaced with the reference values of the new row.

Let's look at an example:

USE tempdb
GO
CREATE TABLE TestCompression (col1 INT, col2 CHAR(50))
GO
INSERT INTO TestCompression VALUES (10, ‘compression testing‘)
GO 5000
-- Original
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = ROW
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = PAGE
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = NONE
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression
GO

SQL Server 2008 row data and page data compression decryption