I have a heap table with around 700k rows. The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME). Why does Data Space Used say this table is taking up nearly 4000000KB (4GB) in Object Explorer Details. It is compressed with PAGE compression.
I know I must be missing something on this calculation because I have another heap table with 12.7 million rows that is only taking up 1.5 GB and it has a row size of 356 bytes. It is also compressed with PAGE compression.
EDIT: I just did an SELECT * INTO to see what the size of this data would be in another table and it's only 28MB...
select sum(page_count) from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.YourTableName'), default, default, 'detailed');(substitute dbo.YourTableName with the appropriate table name)