Freigeben über

SQL Server 2008 Compression feature and INSERTS

  • Artikel

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.

Comments

  • Anonymous
    April 23, 2012
    Hi Shashank, I created a table (HEAP) with Page compression and then inserted 2 million rows to it. I used the syntax INSERT INTO .. WITH (TABLOCK) to make sure all the inserted rows would be Page compressed. But to my surprise after the INSERT when i checked sys.dm_db_index_physical_stats none of the newly inserted rows were page compressed. Could you please help me here, am I missing anything? please respond : dhawalbatavia@hotmail.com Thanks, Dhawal