Enable row degree compression on all tables and also indexes
One of the (optional) needs for the Works With SQL Server 2008 examination is that row degree compression is made it possible for on all tables and also indexes. We have an existing data source with a great deal of tables and also indexes currently developed. Exists a very easy means to enable compression on all these tables and also indexes?
Below is the manuscript I wound up making from splattne is referral.
select 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' from sysobjects where type = 'U' -- all user tables UNION select 'ALTER INDEX [' + k.name + '] ON [' + t.name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' from sysobjects k join sysobjects t on k.parent_obj = t.id where k.type = 'K' -- all keys AND t.type = 'U' -- all user tables
As an apart, take care with making it possible for every little thing to be pressed. The information is pressed in memory and also unwinded every time it is accessed. For an OLTP system with great deals of adjustments and also memory - resident information, compression is not ideal as you'll melt extra CPU for no gain in IOs. For information that reads - periodically, like an information stockroom, it is far more ideal due to the fact that you get a large profession - off in lowered IOs versus the added CPU. Compression is an information warehousing attribute, not an OLTP attribute. Not exactly sure if this relates to you, yet worth mentioning simply - in - instance, and also for others reviewing the string.
Another factor - it might be that you do not get a substantial gain from compression so it isn't worth ending up. Ideal technique to examine the compression gain prior to making it possible for making use of the sp_estimate_data_compression_savings saved - proc.
You need to possibly aim to take care of new tables too, so you do not require to run this set often. I described a method for instantly pressing new tables in this blog post.
I would certainly additionally state that you need to examine to see whether the table is pressed prior to restoring it.
You can utilize this straightforward SQL manuscript in order to create an additional manuscript which needs to get the job done :
select 'ALTER TABLE ' + name + ' REBUILD WITH (DATA_COMPRESSION = ROW)' + CHAR(13) + CHAR(10) + 'GO' from sysobjects where type = 'U' -- all user tables
(I really did not examine this, yet it needs to function.)