Pages in a compressed table may have a combination of row-compressed, page-compressed, and uncompressed data.
For example, if you create the sales_data table:
create table sales_data (isbn bigint not null, au_id varchar(11)not null, total_sales int not null)
And insert this data:
4750984443, '903-94-9344', 34733 2385837442, '346-94-5593', 50945 2388347442, '346-94-5593', 50945
sales_data is uncompressed:
However, if you alter sales_data for
compression:
alter table sales_data set compression = row
And insert this data:
4783023685, '887-49-9984', 45009 3894350422, '776-45-9045', 89667 3349580094, '884-59-9983', 84855
Only the new data is compressed:
However, if you alter sales_data again
to be uncompressed:
alter table sales_data set compression = none
And insert this data:
6590345093, '439-49-9943', 485844 3458940330, '559-40-3999', 21003 4859390403, '884-30-0200', 790499
Adaptive Server does not compress the new data, but retains the older data in a compressed state:
sp_help reports whether a table has ever contained compressed data. This is the sp_help output for sales_data:
Name Owner Object_type Object_status Create_date ---------- ----- ----------- ------------------------ ------------------- sales_data dbo user table contains compressed data Apr 8 2011 4:36PM