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.

from   sysobjects   where  type = 'U' -- all user tables
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
2019-12-02 02:56:49
Source Share
Answers: 3

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.

Many thanks

2019-12-03 05:48:20

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.

2019-12-03 04:49:38

You can utilize this straightforward SQL manuscript in order to create an additional manuscript which needs to get the job done :

         + CHAR(13) + CHAR(10) + 'GO'
   from   sysobjects
   where  type = 'U' -- all user tables

(I really did not examine this, yet it needs to function.)

You can locate a far more sophisticed manuscript below on the SQLServerBible site (seek "db_compression procs".) Read the writer is post "Whole Database - Data Compression Procs".

2019-12-03 04:33:34