Table Data Space Used Confusion

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...

The solution was to create a clustered index on the table which immediately shrank the data size down to 13MB. I found this out thanks to the following post on StackOverflow: https://stackoverflow.com/questions/3336934/reduce-sql-server-table-fragmentation-without-adding-dropping-a-clustered-index


The point is, you have substantial fragmentation on the Heap. You keep calling it a "table", but there is no such thing at the physical data storage or structure level; a table is a logical concept, rendered physically as:

• either the Heap plus all Nonclustered Indices plus Text/Image chains

• or the Clustered Index plus all Nonclustered Indices plus Text/Image chains.

Heaps get badly fragmented; the more interspersed (random) Insert/Deletes/Updates there are, the more fragmentation.


There is no way to clean up the Heap, as is. MS does not provide a facility (other vendors do).

StackExchange Database Administrators Q#68213, answer score: 3