TSQL Row Compression

The first part: conceptual understanding

1,row Compression is to store fixed-length types as variable-length storage types, and for developers, Row Compression is transparent and requires no application changes.

1.1 For character type

Char (200), which is a fixed-length data type, but may not store 200 characters when it is actually stored. When the data is physically stored, SQL Server then complements the space to reach 200 characters. If you convert it to varchar (200), you can save storage space without having to fill in the blanks.

1.2 For numeric type

Before SQL Server 2005 SP2, the decimal type is always stored as fixed data. Depending on the accuracy of the values, each decimal value requires 5 to 17 bytes of space. The newly introduced vardecimal storage format is to store the decimal value in a variable-length format. This format removes 0 of the decimal value before and after it, which reduces the space required for storage.

SOL Server 2008 Data compression extends this functionality by processing all fixed-length data types, including integer, Char, and float. The data is now not stored in a fixed-size byte, but with the smallest desired byte. Developers do not need to modify the data type, only need to enable row compression, SOL Server 2008 will use the smallest variable data type to store data.

1.3 Row compression cannot process XML, BLOB, and Max data types

Part Two: Enable row compression on a table

2, two ways to enable the table row compression

2.1 When creating a table, enable row compression

CREATE TABLE dbo. Testcompression (    intnotnullidentity(1,1 ),    CHAR(notnull)with ( Data_compression=ROW)

2.2 Enable row compression for tables that do not have row compression enabled

-- change compression options, update to row, and compress table data ALTER TABLE dbo. Testcompression REBUILD with (data_compression=

3. View the storage space saved by data compression

EXECsys.sp_estimate_data_compression_savings@SCHEMA_NAME='DBO',       @OBJECT_NAME='testcompression',       @INDEX_ID=NULL,       @PARTITION_NUMBER=NULL,       @DATA_COMPRESSION='ROW'

4. View the storage space already used by the table

-- View the storage space you have used EXEC ' testcompression '

5, the sample code is as follows

CREATE TABLEdbo. Testcompression (IDint  not NULL Identity(1,1), JDCHAR( -) not NULL) with(data_compression=ROW)--change compression options, remove compression optionsALTER TABLEdbo. Testcompression REBUILD with(data_compression=None)Declare @i int=0 while @i< -begin    INSERTtestcompression (JD)VALUES(REPLICATE('a', -))    Set @i=@i+1EndEXECsys.sp_estimate_data_compression_savings@SCHEMA_NAME='DBO',       @OBJECT_NAME='testcompression',       @INDEX_ID=NULL,       @PARTITION_NUMBER=NULL,       @DATA_COMPRESSION='ROW'--View the storage space you have usedEXECsp_spaceused'testcompression'--change the compression options and row compression for the tableALTER TABLEtestcompression REBUILD with(data_compression=row)--View the storage space you have usedEXECsp_spaceused'testcompression'

Part III: Index compression

6, modify table results, add a column, create an index on the column

-- Add a column Alter Table dbo. TestcompressionAddchar(varchar, getdate (),(), ()--- CREATE index  on Testcompression (TestDate)

7. View the storage space used before index compression

-- View the storage space you have used EXEC ' testcompression '

8, row compression of the index

-- to compress an index ALTER INDEX  on [dbo]  =all with= row  )

9, view the storage space occupied by the index after compression

-- View the storage space you have used EXEC ' testcompression '

10, the sample code is as follows

--Add a columnAlter Tabledbo. TestcompressionAddTestDateChar( $)default Convert(varchar,getdate(), the)--Create an indexCreate IndexIdx_testcompression_1 ontestcompression (testdate)--View the storage space you have usedEXECsp_spaceused'testcompression'--to compress an indexALTER INDEXIdx_testcompression_1 on [dbo]. Testcompression REBUILD PARTITION=  All  with(data_compression=row)--View the storage space you have usedEXECsp_spaceused'testcompression'

PS, full code

CREATE TABLEdbo. Testcompression (IDint  not NULL Identity(1,1), JDCHAR( -) not NULL) with(data_compression=ROW)--Change compression OptionsALTER TABLEdbo. Testcompression REBUILD with(data_compression=None)Declare @i int=0 while @i< -begin    INSERTtestcompression (JD)VALUES(REPLICATE('a', -))    Set @i=@i+1EndEXECsys.sp_estimate_data_compression_savings@SCHEMA_NAME='DBO',       @OBJECT_NAME='testcompression',       @INDEX_ID=NULL,       @PARTITION_NUMBER=NULL,       @DATA_COMPRESSION='ROW'--View the storage space you have usedEXECsp_spaceused'testcompression'--Change compression OptionsALTER TABLEtestcompression REBUILD with(data_compression=row)--View the storage space you have usedEXECsp_spaceused'testcompression'--Add a columnAlter Tabledbo. TestcompressionAddTestDateChar( $)default Convert(varchar,getdate(), the)--Create an indexCreate IndexIdx_testcompression_1 ontestcompression (testdate)--View the storage space you have usedEXECsp_spaceused'testcompression'--to compress an indexALTER INDEXIdx_testcompression_1 on [dbo]. Testcompression REBUILD PARTITION=  All  with(data_compression=row)--View the storage space you have usedEXECsp_spaceused'testcompression'

TSQL Row Compression