I'm using SQL Server 2005 buld 3080.
I have a table with no clustered index that return the followings results on sp_spaceused:
Rows: 47841318 Reserved :18015392 KB Data : 7801184 KB Index : 2489536 KB Unused :7724672 KB
Tha data takes about 8 gb and there is a space lost of about 7.5 gb on the 17 gb reserved. I understans that because this is a HEAP table it cannot been defrag when running a rebuild index on it. Then I created a clustered index on that table to release
unused space. Here is the new result of the sp_spaceused:
Rows: 47841318 Reserved: 15865744 KB Data : 13140608 KB Index: 2725000 KB Unused: 136 KB
Now we can see that the unused space as been released with the defragmentation.
I don't understand why Data space has almost double it's value. The index size has grown up a little bit due to my new clustered index (Probably to store the root levels of the index). I expected that data remain approximatly the same.
If somebody could explain this I would really appreciate to understand.
View Complete Post