I was trying to implement 'Row' data compression on sql server data table. After successfully executing script on one data table which was having 116000 records and 30 columns (10-15 columns could be eligible for Row data compression) I follow below steps for physical memory consumption verification:
Step 1) I calculate storage space for all my data file(mfd,ldf and ndf) for data base in drive .which comes out to be 6.63 GB.
Step 2) Successfully executed Row data compression script e.g.
USE [DbName]
ALTER TABLE [dbo].[TableName] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)
Step 3) follow step 1) and it comes out to 6.66GB. There was no single transaction and data insertion between these steps.
Am i doing wrong verification or i have wrong thinking about data compression.
Assumptions: Data columns with Int(4 byte) data type and storing value less than 100 will be compressed to store in 1 byte(small Int) of space. Similarly for other data columns.