In my previous article SQL SERVER – 2008 – Introduction to New Feature of Backup Compression I wrote about Row Compression and I have received many request to write in detail about Row Compression. I like when I get request about any subject to write about from my readers.
Row Compression feature apply to zeros and null values and optimize their space in SQL Server. In fact, due to Row Compression feature SQL Server does not take any disk space for zero or null values. Any datatypes (decimal, datetime, money, int etc) if they are storing zero or null values in their rows, Row Compression feature does not store them in database at all.
Let us apply Row Compression to one of the table in database and see the performance with regards to storage compression. We can check beforehand about how much compression it will provide using stored procedure sp_estimate_data_compression_savings. Let us try to apply compression on AdventureWorks database table Production.WorkOrder.
sp_estimate_data_compression_savings 'Production','WorkOrder',NULL,NULL,'ROW'
Above SP will give you as many as resultset as many are index on table. Resultset of above SP will tell few of the statistic like current compression, future compression after requested compression, current rows and future reduced rows after requested compression. If you see reduction in the size of the table, you should go ahead and apply the row level compression.
To apply row level compression whole table needs to be REBUILD with Data_Compression = ROW clause enabled. Following command is used to rebuild the table.
ALTER TABLE Production.WorkOrder REBUILD
WITH (DATA_COMPRESSION = ROW)
After applying row compression size of the table is reduced by more than 1MB in size.This is one of the most valuable feature of SQL Server 2008 and I am going to use this feature on our production server as soon as we will upgrade our servers.
Additionally, there is concept of Page Compression as well and it is useful to do when there is lot of frequently occurring data. I will run few tests on live server and will post analysis when either of them should be used. I suggest trying with Row Compression first and take advantage of this new data storage improvement feature.
Reference : Pinal Dave (https://blog.sqlauthority.com)
