compression part 2

Compression in partitions

We can set compression property for individual partition .

Below are the points that we have to take consideration for parition operation.



1.Merging
When two partitions are merged , resultant partition inherits compression property of destination
partition
2.merging
When two partitions are splitted , new partition will have data compression settings of source
partition
3.switching
When partitions switched between two tables both table have same data compression for the related
partition


Let see it with more examples




CREATE PARTITION FUNCTION part_function (datetime)
AS RANGE LEFT FOR VALUES ('2011-01-01','2011-01-02','2011-01-03') ;
GO

CREATE PARTITION SCHEME part_scheme
AS PARTITION part_function
TO ([primary], [primary] , [primary], [primary] ) ;
GO


CREATE TABLE sales
    (
         id           INT IDENTITY(1, 1),
         sales_date   DATETIME,
         product_id   INT,
         sales_amount INT
    )
ON part_scheme(sales_date)


SELECT *
FROM   sys.partitions
WHERE  object_id = Object_id ('sales')







ALTER TABLE sales
REBUILD  WITH (data_compression = page)

SELECT *
FROM   sys.partitions
WHERE  object_id = Object_id ('sales')




Now once again we rever data compression

ALTER TABLE sales
REBUILD  WITH (data_compression = NONE)


Now we  will different partition with different compression settings

ALTER TABLE sales
REBUILD PARTITION = ALL
WITH (data_compression = page on partitions (1,2),
     data_compression = row on partitions (3,4) )
or

ALTER TABLE sales
REBUILD PARTITION = ALL
WITH (data_compression = page on partitions (1 to 2),
     data_compression = row on partitions (3 to 4) )



now once again run query

SELECT *
FROM   sys.partitions
WHERE  object_id = Object_id ('sales')