Creating a Compressed Table

You can compress all tables except system and worktables.

Use create table to create a compressed table or partition. 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.

The partial syntax for compression is:

create table [database.[owner].]table_name
(column_name datatype ...
	   [not compressed ],
 [, next_column...])
[with {max_rows_per_page = num_rows,
	...
     compression = {none | page | row }]
[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, Adaptive Server creates the table using page-level compression.

To compress all columns in the sales table, use:

create table sales
   (store_id int not null,
   order_num int not null,
   date datetime not null)
with compression = row

To compress only the order_num column, specify the other columns as not compressed:

create table sales
(  store_id int not null not compressed,
   order_num int not null,
   date datetime not null not compressed)
with compression = row

To use page-level compression on the Y2008 partition and row-level compression on the Y2009 partition, enter:

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)

Use sp_help to view a table's compression level. This is the sp_help compression information for the mail table:

 Name   Owner   Object_type   Object_status         
         Create_date
 ----   -----   -----------   ----------------------------------------------
         -------------------
 mail     dbo    user table   row level compressed, contains compressed data
         Apr  8 2011  2:55PM