SQL Server 2008 Compression feature and INSERTS

If you a heap (table without indexes) configured for page-level compression, pages receive page-level compression only in the following ways:

  • Data is inserted by using the BULK INSERT syntax
  • Data is inserted using INSERT INTO... WITH (TABLOCK) syntax
  • A table is rebuilt by executing the ALTER TABLE....REBUILD statement with the PAGE compression option

When you do normal inserts (i.e. none of the above), the new rows will not be compressed, unless the rows are inserted on pages that are already compressed. For instance, suppose you have a heap with 4 pages. Initially, the pages are not compressed. Next you do ‘ALTER TABLE … REBUILD’ and the 4 pages get compressed to 2 pages. Suppose now new inserts happen, and the first insert goes to one of the existing pages. Because the page is already compressed, the new row will be compressed. However, when the two compressed pages get full, a new page will be allocated, which will not be compressed.

To recompress the heap, you can do ALTER TABLE heap REBUILD WITH (DATA_COMPRESSION=PAGE).

 

The reason we cannot compress heaps during normal inserts, has to do with space reservation for heaps.

 

So the new records will not be "PAGE" compressed, i.e. it won't be prefix or dictionary compressed, but the values will still be "ROW" compressed. So there is still benefit of the compression but not the full page level compression.

 

Note: This only affects heaps, if the table is converted to have a clustered index this is not a issue.