http://sqlblog.com/blogs/linchi_shea/archive/2008/06/20/sql-server-2008-data-compression-impact-of-data-distribution.aspxIn this post, I continue to explore the implications of SQL Server 2008 data compression. It is particularly worth highlighting the fact that SQL Server 2008 data compression is performed at the page level instead of the table level. In other words, whenenCommunityServer 2.1 SP2 (Build: 61129.1)http://sqlblog.com/blogs/linchi_shea/archive/2008/06/20/sql-server-2008-data-compression-impact-of-data-distribution.aspx#7448Mon, 23 Jun 2008 15:01:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7448Chris Culler<p>The example was crafted to show the effect of page-level compression, but I would have preferred an example that belonged in the world of real RDBs. &nbsp;A lengthy column of only 80 distinct values among 8 million rows screams &quot;normalize me!&quot; &nbsp;Doing so would achieve far better than 10:1 compression, and with 1970's &quot;technology.&quot;</p> http://sqlblog.com/blogs/linchi_shea/archive/2008/06/20/sql-server-2008-data-compression-impact-of-data-distribution.aspx#7559Sat, 28 Jun 2008 17:15:29 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7559Linchi Shea<p>Chris;</p> <p>For this example, a table-level compression approach (such as what is used by DB2 v9.5) would indeed achieve better compression ratio. But there are pros and cons between table-level compression and page/block-level compression. For instance, the table level compression is sensitive to the initial sample. If the initial sample is not representative, the compression ratio can be very bad. I understand that it can be corrected with a reorg later on. But reorg is a major operation.</p> <p>Also note that the example applies to Oracle as well, as it uses a block-level compression algorithm.</p>