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)









No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts