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 INDEX idx_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)