Is there a way to set the default compression level for all tables created in a database?

Question

Is there a way to set the default compression level for all tables and indexes created in a specific database?

We use a product that given an input table will create several output tables in the same database.  Testing has revealed that due to the nature of the data, and size of the tables we would see about a 50% disk space savings by compressing all the tables, without a significant impact on query performance.

We can not change the product to create the tables with compression turned on. 

Is there a way to turn on compression for tables and/or indexes on a database level?  I've searched and haven't been able to find one.

If not we could alter and rebuild the tables with compression on after the product is finished. 



 

Answer 1

Assuming SQL Server 2008, yes you can use ALTER TABLE to compress and existing table:

ALTER TABLE <tablename> REBUILD WITH (DATA_COMPRESSION=PAGE);

ALTER TABLE (Transact-SQL)

The same applies to Indexes:

ALTER INDEX <indexname> ON <tablename> REBUILD WITH (DATA_COMPRESSION=PAGE);

ALTER INDEX (Transact-SQL)

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 2

I don't believe there is any way to set a global database default value for new tables, and there would be no option to automatically turn on compression for all tables and indexes due to the uncontrolled nature of this (transaction logs growing, etc).

Instead, you can knock up a script that will get a list of all tables and indexes and execute the ALTER TABLE or ALTER INDEX commands that Jonathan suggested.

Answer 3

Jonathan and Jim are right - there is no way to do this using (out of the box) options.

This would be a good time to use a DDL trigger though.....

CREATE DATABASE CompressionTest

GO

USE CompressionTest

GO

ALTER TRIGGER CompressTablesAfterCreation

ON DATABASE

AFTER CREATE_TABLE

AS

BEGIN

DECLARE @TableSchema sysname, @TableName sysname

SELECT  @TableSchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','sysname'),

@TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

DECLARE @SQL nvarchar(2000)

SET     @SQL =  'ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' +

QUOTENAME(@TableName) + ' REBUILD WITH (DATA_COMPRESSION=PAGE)'

EXEC    (@SQL)

END

GO

CREATE TABLE CompressMePlease

(

Col1 int,

Col2 varchar(50)

)

--Check it out

SELECT OBJECT_NAME(object_id) AS [ObjectName], [rows], data_compression_desc, index_id

FROM sys.partitions

WHERE data_compression > 0

ORDER BY ObjectName;

--When you're done....

--DROP DATABASE CompressionTest

Any time you create a new table, the DDL trigger will "rebuild" it immediately with page compression (as part of the same transaction).

I feel a blog article coming on....  ;-)

HTH.

---

Aaron Alton | thehobt.blogspot.com

Answer 4

Hi guys,

All's suggestions are good.

Anyways,I think the trigger Aaron provided changes to "AFTER CREATE TABLE, ALTER TABLE" may be better due to "If not we could alter and rebuild the tables with compression on after the product is finished. ".

Thanks,
Jin 

---

Jin Chen - MSFT

Answer 5

I wish the forums would send alerts more consistently than they do, or I would have replied to this a lot sooner.

I'd have to agree that Aarons DDL trigger looks like a very good solution to your problem automagically.  I guess my only real question would be how are you creating tables, because if you use a SELECT INTO:

SELECT *

INTO dbo.SelectIntoTest

FROM AdventureWorks.Sales.SalesOrderDetail

You get the following error:

Msg 539, Level 16, State 78, Line 1
Schema changed after the target table was created. Rerun the Select Into query.

So you wouldn't be able to use it in that case, but otherwise I haven't been able to break it, which is always my first thing to test when dealing with triggers.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 6

Thank you it looks like the triggers might work. 

I am creating a couple tables later in the process with a select into, but if I have to I can change those.