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 (http://blog.SQLAuthority.com)
Is there a performance difference when using row compression? Surely there are space savings, but you never get something for nothing. I imagine there is a small computational cost that is attached to these storage savings.
Very interesting
[...] Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar. (Read More Here) [...]
Thanks Sir,
its very good.
i want indetail about the Dirrential backup.
but i don’t know how to use that.
i am daily getting the full backup but it takes so much of time.
Please Consider this sir,
Sir
It is really helpful for me. I have gone through all of sql server 2008 interview questions. The way you are explaining is really excellent. Please publish more questions.
please send me querry to execute an dapply row compression. I am not able to apply above querry properly
kindly send me proper syntex and method to compress it
Hi Ashish,
The comprassion can be enabled to new tables/nex as well as to existing table/index. The syntax to enable the comprassion is follwoing.
CREATE TABLE TableName
(
column defination,….
)
WITH (DATA_COMPRESSION = ROW|PAGE|NONE)
CREATE INDEX IndexName ON TableName (column,…) WITH (DATA_COMPRESSION = ROW|PAGE|NONE)
ALTER TABLE TableName REBUILD WITH (DATA_COMPRESSION=ROW|PAGE|NONE)
ALTER INDEX IndexName REBUILD
WITH (DATA_COMPRESSION=ROW|PAGE|NONE)
Please let us know what is the error you are getting.
Kind Regards,
Pinal Dave
[...] SQL SERVER – 2008 – Introduction to Row Compression [...]
Dear Pinal
After the row or page compression I/O performance will increase but what about the cpu performance. How to decide on page or row compression which should not affect the cpu performance much.
Please provide any article on deciding the row and page compression selection which show I/O and cpu performance statistics also.
Please tell how can i get a list of compressed tables in the database ?
Thank you in advance
Hi
–display all the compressed tables
select a.name,b.data_compression_desc from sys.partitions b
inner join sys.tables a on
a.object_id = b.object_id where data_compression 0
or
select a.name,b.data_compression_desc from sys.partitions b
inner join sys.tables a on
a.object_id = b.object_id where data_compression_desc ‘NONE’
please u can check.
Ananthesh
[...] Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed-length strings are stored in variable-length storage format, just like Varchar. (Read more here) [...]