Data Page Level Compression on a Heap Table with Statistics

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'DBArchive'),object_id('FactMTDPrior_Dec2010'), 0, NULL , 'DETAILED');

Compression Whole Table
USE DBArchive
go
ALTER TABLE FactMTDPrior_Dec2010
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

Compressing Specific Partition
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO


Important values are taken into consideration

Pagecount Min Max Avg Compression page count
Record Size
b4e compression 1945864 171 174 171.987 0
after compression 777966 29 133 63.759 777959
after comp & shrinking 800030 29 134 65.574 759933




Before Compression
dbarchive.mdf 15,586,304 kb
dbarchive.ldf 353,216 kb


After Compression
dbarchive.mdf 21,815,296 kb
dbarchive.ldf 353,216 kb


After Shrinking the compressed database
dbarchive.mdf 6,402,560 kb
dbarchive.ldf 1,024 kb


1. (Pagecount before compression / Pagecount afer page compression and shrinking)
(1945864.00 / 800030.00) = 2.43223879104
Conclusion >>> We can Say PageCount was reduced to 60% after applying Page Level Compression and Shrinking on the Heap Table (which does not have any indexes)

2. Calculated the mdf file size after pagelevel compression and shrinking.mdf file reduced by 60% on the Heap Table (which does not have any indexes)
select 6402560 * 2.43223879104 = 15572554.79 (almost equal to 15,586,304 kb)

Partition Number for a specific Partition Table

select PartitionNbr = $Partition.PartitionFunction('01/01/2011')

Merge / Split Partition Script

DECLARE @DateDt Datetime
, @SQLTXT VARCHAR(256)
SELECT @DateDt = '2011-12-26T00:00:00.000'

while @DateDt < '01/01/2012'
begin
ALTER PARTITION SCHEME SCHEME_SnapShotDt NEXT USED FG_SnapShotDt
ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() SPLIT RANGE (@DateDt);
--ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() MERGE RANGE (@DateDt);
SELECT @DateDt = @DateDt + 1
SELECT @DateDt
end