Clustered Tables vs Heap Tables(Free Space Reuse)
在很多書上都看過當資料表為Heap時會因為不會Reuse 經DML後而Table已經沒在使用的空間，
導致Heap的資料表很容易有虛胖情形(明明資料量很少但卻占掉很大的空間)。但昨天在另一本書
上看見另一種說法表示Heap的資料表是會Reuse Free Space的，為了一探究竟，來做個小LAB吧。
⇒如下圖所示，我先建立兩個資料表HeapTable跟ClusteredTable。其中ClusteredTable是有
建立Clustered Index的(Clustered Index欄位是id，流水號)。完成建立Table後，則在每張資料表
各塞入10000筆資料。
⇒塞完資料後我們利用DBCC SHOWCONTIG來檢視一下兩張資料表的頁面配置。HeapTable
用了33個Page，ClusteredTable用了34個Page(如下圖紅色圈選處)。
⇒這時候我們將兩張TABLE的資料全部刪除(注意 : 請使用Delete而非Truncate Table)
⇒刪完資料後我們重新用DBCC SHOWCONTIG檢視這兩張資料表，我們發現在沒有任何
資料的情況下HeapTable還占著20個Page而ClusteredTable只剩下1個Page。在此一步驟中
可以發現HeapTable在Free Space的釋放比ClusteredTable差。
⇒利用sp_spaceused來檢視兩張資料表可以更明顯看出，兩張表的rows欄位都是0了，
但是HeapTable的data欄位顯示資料表還是占了160KB。
⇒那該如何讓HeapTable吐出並沒有在使用的空間呢?我在底下的圖中是去Rebuild該Table(紅色圈選處)，
但是也有其他方式有相同作用。例如在HeapTable上建立Clustered Index或是重新產生一張空的資料表並將
HeapTable的資料倒進去等等，不外乎都是重建資料的動作。經過Rebuild後，我們可以看見HeapTable的
頁數由20個Page下降為1個Page。
⇒既然HeapTable很難吐出Free Space，但對於這些空間它會去Reuse嗎?如果占著空間又不使用，
真的就是占著茅坑不拉屎了。因此我同樣塞入10000筆資料後再刪除5000筆(id欄位是奇數)。如下圖
所示兩張表占的頁數不變，但是每一頁的密度都只剩下一半(如下圖藍色圈選處)。
這表示有一半的空間是Free的。
⇒然後我們再塞回5000筆資料，看看空間的使用變化。
⇒塞回5000筆後的空間變化如下，HeapTable占了37個Page而ClusteredTable占了50個Page。
HeapTable由33個Page成長到37個Page，ClusteredTable由34個Page成長到50個Page。由這樣
的變化可以知道HeapTable是會Reuse Free Space的，至於為何ClusteredTable為何會增加Page數
那麼多呢?因為ClusteredTable是依照id這一個欄位來排序，而id欄位是流水號。之前被我們刪除的奇數
號範圍是1 – 10000中，而後來新增的5000筆資料編號會是10001 – 15000，因此是新增Page來存放並
依照id排序新資料。
結論 : 由這一次的實驗結果來看，Heap的資料表是會Reuse Free Space的，但為什麼總是會有人提到Heap資料表會虛胖呢?
由這一個實驗也可以發現Heap不會有效的吐出沒在使用的空間，因此當我們針對Heap資料表有大量的資料刪除等動作時
(例如刪除某年度資料)，那些空間並不會釋放出來，還是會被Heap資料表占住。這時候就會發現資料筆數明明不多但為何
Table使用那麼多空間。而Clustered資料表就沒有這樣的問題，一旦Page中沒有資料就會將該Page釋放出來。
補充 : 經SQL Server MVP James Fu 指點，補充了一些資料
1. Heap 有可能發生更大的破碎在於大量 INSERT , 利用 INSERT INTO .... SELECT .,,, 指令的時候
可能會因為平行處理而造成同時 INSERT 到多個 PAGE而引發更大的破碎。
2.Alter Table REBUILD，如果資料很多且有其他 Index 的時候不見得會壓縮起來。因為本次測試的時
候是沒有建立索引 , 且資料表內沒有資料因此這樣的測試狀況可能不準。之前James遇到的狀況是在
我是ROCK
rockchang@mails.fju.edu.tw
