Compression Part 1

If you are using Enterprise or Developer edition of SQL Server  2008 you can use compression on your server

What we can compress?
We can compress
Table
Index
Any partition from partition Table of Index. We can set different compression type for different partition

compression has two types
1.ROW
2.PAGE

Table compression and index compression are separate. Compressing  a table does not automatically compress index also

syntax for compression
1.
ALTER TABLE

REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  )

2.ALTER TABLE

REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ON PARTITIONS(),
… )

Lets see it with Examples.

drop table sales

CREATE TABLE sales

    (

         salesid    INT IDENTITY(1, 1),

         product_id VARCHAR(50),

         amount     INT

    )

DECLARE @i INT

DECLARE @num INT

SET @num = 100

SET @i = 10

WHILE @i <= 100000

    BEGIN

        INSERT INTO sales

        SELECT ‘product’ + CONVERT(VARCHAR(10), CONVERT(INT, Rand() * @num)),

               Rand() * @i

        SET @i = @I + 1

    END

CREATE CLUSTERED INDEXidx_sales_porduct_id

    ON sales (product_id)

CREATE INDEX idx_sales_sales_id

    ON sales (salesid)

EXEC Sp_spaceused sales

SELECT *

FROM   sys.partitions

WHERE  object_id = Object_id(‘sales’)

It will show data compression as NONE

Now lets compress this table

ALTER TABLE sales

rebuild WITH (data_compression = page)

exec sp_spaceused sales

SELECT *

FROM   sys.partitions

WHERE  object_id = Object_id(‘sales’)

Now we can see data compression is PAGE

We can also specify data_compression while creating a table
Lets see

CREATE TABLE test

    (

         id   INT,

         data VARCHAR(100)

    )

with (data_compression = page)