Enabling Data Compression On All Tables By Default in a SQL Server 2008 Database

Jonathan Kehayias — Tue, 24 Feb 2009 16:35:24 GMT

One of the great things about answering questions online is that every now and then you get one of those Aha! moments and you create a solution to a problem that once written seems really obvious and makes you wonder how that ever escaped you previously. This happened recently on the MSDN Forums, but in this case it wasn't my Aha! moment, it was Aaron Alton's in a follow up response he posted behind me. The question asked was:

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

I misread the question initially and thought that it was a two part question, first asking if it could be set by default and then if not, could it be changed afterwards, so I responded with how to alter the table and indexes afterwards since there isn't a default option to set it for all tables. Later Aaron replied how to use a DDL Trigger to automagically add Database Compression to all tables. He followed up today with a blog post titled:

Enabling Data Compression By Default in a SQL Server 2008 Database

Its an interesting solution to the problem posed by the original poster, and when I read it in the forums, my first thought was Duh! Why in the world didn't that cross my mind? I'll never know but it was definitely a Aha! type of thing. There are some caveats to this solution, which you can read about in his blog post, or on the original forums thread:

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

The Rambling DBA: Jonathan Kehayias : Compression

Enabling Data Compression On All Tables By Default in a SQL Server 2008 Database

Enabling Data Compression By Default in a SQL Server 2008 Database