You can compress all tables except system and worktables.
Use create table to create a compressed table, partition, or to designate index compression for a table. You need not compress all columns in a table. When designing your table, select the columns that offer the greatest benefit from compression. Partitions, and tables can use row- and page-level compression. Partitions for which you do not specify the compression level inherit the table-level compression.
Specifying compression at the index level overrides index compression specified at the table level. Local index partition level specification overrides the index-level specification.
create table [database.[owner].]table_name (column_name datatype ... [not compressed ], [, next_column...]) [with {max_rows_per_page = num_rows, ... compression [= {none | page | row }] index_compression [={none | page} ] [on segment_name] [partition clause] partition_clause::= partition by partition_type [(column_name[, column_name]...)] ([partition_name] ... [with compression = {none | page | row }] [on segment_name], [, next_partition...])
The create table. . . with compression parameter overrides the database-wide setting. That is, if you create a database with row-level compression, then issue a create table command that indicates page-level compression, the table is created using page-level compression.
create table sales (store_id int not null, order_num int not null, date datetime not null) with compression = row
create table sales ( store_id int not null not compressed, order_num int not null, date datetime not null not compressed) with compression = row
create table sales_date (store_id int not null, order_num int not null, date datetime not null) partition by range (date) (Y2008 values <= ('12/31/2008') with compression = page on seg1, Y2009 values <= ('12/31/2009') with compression = row on seg2, Y2010 values <= ('12/31/2010') on seg3)
Name Owner Object_type Object_status Create_date ---- ----- ----------- ---------------------------------------------- ------------------- mail dbo user table row level compressed, contains compressed data Apr 8 2011 2:55PM