SQL server includes a feature that allows to compress the data at different levels. Like tables and indexes. We can compress a table that is stored in a heap, a table that has a clustered index. Compression is not available for system tables.We can configure the compression option for each partition in the partitioned table or indexes.
The compression rate depends upon two factorsData characteristicsCorresponding data types.ALTER TABLE or ALTER INDEX statements are used to change the compression state of a table, index or partition. We cannot enabled a table for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes.Data Compression is helpful to reduce the size of the database and improve the performance of I/O workloads.Basically there are two types of Data compression that is usedRow compressionPage CompressionRow Compression:It changes the data storage format i.e; it first identifies the data type of each column and then converts it to variable length so that it reduces the amount of required storage increases the amount of data stored on a page. It may reduce the amount of metadata associated with a record.
Even though compression is on fixed data types the actual schema will not change.Example:Suppose if we have a table with column FIRSTNAME with datatype CHAR(30). So this will be fixed datatype. Now if you insert data into this column like KRISHNA then the column uses only 7 characters remaining 23 characters are empty but space is allocated because of fixed data type.
To avoid this we can use ROW level Compression which remove these extra bytes from the column so that free space can be saved. If the same thing is repeated for N number of rows we can get more free space.To enable Row Compression on a table/indexSyntax:ALTER TABLE [or INDEX] tablename[or indexname] REBUILD WITH (DATA_COMPRESSION = ROW)Example:Enabling the Row Compression on employee tableALTER TABLE employee REBUILD WITH (DATA_COMPRESSION = ROW)We can also compress a table while creating it.
Example:Row compress a table during the creation of employee table.USE sample;CREATE TABLE employee (emp_no int PRIMARY KEY, emp_fname char(50), emp_lname char(50)) WITH (DATA_COMPRESSION = ROW)This compress the clustered index on the table, if it is not exist then it will compress the heap.Page Compression:Page compression is used to reduce column redundancy in one or more rows in a data page. Page compression is extension of row compression with additional steps.
Page compression involves following stepsROW compressionPrefix compressionDictionary compressionIn Prefix compression phase a value is identified for each column which is used to reduce the storage space for the values. Prefix values is created for each column and stored in compression information which follows the page header. The repeated prefix values in the column are replaced with a reference to the corresponding prefix.Unlike the Prefix compression Dictionary compression searches for repeated values anywhere on the page instead of one column.To enable Page compression on table or index:Syntax:ALTER TABLE [or INDEX] tablename[or indexname] REBUILD WITH (DATA_COMPRESSION = PAGE)Example:Enabling the Page compression on employee table.
ALTER TABLE employee REBUILD WITH (DATA_COMPRESSION = PAGE)Page compression is applied only after a data page is full, while ROW compression is applied immediately.