Data Compression is the vital feature of SQL Server 2008, and it helps to improve the performance of the SQL Server without modifying the code of the application. We can reduce the storage of the database by using data compression, with this Input /Output reading can decrease because the data stored in fewer pages which are compressed so, queries required to read that fewer pages, hence it improves the I/O performance. When we do the data compress and decompress, it will take more CPU resources. There are two types of data compressions row compression and page compression (Louis, 2014).Row Compression:The row compression will change data storage format. Row compression recognizes the every column data type and changes it into variable length as all we know that variable length saves space. So using row compression can decrease storage space.
Row compression minimizes the overhead metadata related to record (Dave, 2016). For instance, if we have name field with variable length data type of char (50). When we store the name of SHLOKA, this title requires only six characters it uses just that space and remaining space will be removed in row level compression hence that extra bytes of space will be saved (Vidhyasagar, 2009). In Row level compression zero and NULL values will not take any space.The below is a T-SQL example for row compression while creating new table.
Use CIS605GOCreate Table Test_compression(docid int,Name char(50))WITH (DATA_COMPRESSION = ROW);GOPage Compression:Page level compression starts with row level compression. The column redundancy can be minimized using page level compression in one or many rows. After row compression, prefix compression will start. In prefix, each column is checked for shared values and store them in the first row of the below header. The recognized prefixed value replaced with pointers.
Dictionary compression is the last step in page level compression, it scanned for the repeated column values on the total page rather than on single column and stored in below the prefix row (Dave, 2016).Example:Use CIS605GOCreate Table Page_compression(docid int,Name char(50))WITH (DATA_COMPRESSION = PAGE);GOTable Compression:By using the data compression wizard, tables can be compressed. Table compression can be achieved with the help of non-clustered or clustered index.Example:use CIS605Alter Table page_compression REBUILD PARTITION = ALL WITH(DATA_COMPRESSION = PAGE) EXEC sp_spaceused page_compression Output:name rows reserved data index_size unusedPage_compression 1 72 KB 8 KB 8 KB 56 KB
Best services for writing your paper according to Trustpilot
