一般對于沒有用的資料,都會經行删除,而删除通常使用的是DELETE和TRUNCATE指令。對于有條件地删除,基本上就會使用DELETE,當然還是沒有絕對,用TRUNCATE也可以實作,隻要把【不需要】删除的資料插入新表,然後truncate源表,再把資料導回來或者直接重命名新表就可以了。
下面例子主要比較全表删除的情況下DELETE
和TRUNCATE 之間的差異:
首先,先建立測試用例:本例使用AdventureWorks資料庫。先建立3個表:
檢視一下各個表的索引情況:
結果:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuU2YlR2N2EDOhNjZ3EGNkZzNlVGOzI2Y5ETOkZWMiFDMfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
然後,用DELETE對三個表進行清空操作:
使用DBCC SHOWCONTIG指令來檢視資料分布情況:
結果如下:
從上圖可以看出,堆表(即沒有聚集索引的表)掃描出82個頁和11個區,由于已經删除屬于,是以這些都是空的。而有聚集索引的表,隻有1個頁和1個區。有非聚集索引的表,也有66個頁和9個區。
可以看到,沒有聚集索引的表删除資料後還遺留了不少空間。
下面來看看TRUNCATE操作:
同樣,先建立表,使用上面的建表語句建立同樣的表,以保證對比一緻性:
然後檢視相關索引:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuU2YlR2N2EDOhNjZ3EGNkZzNlVGOzI2Y5ETOkZWMiFDMfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
現在進行清空操作:
再檢查資料分布情況:
可以看到,3個表都已經沒有頁和區了。
通過上面的對比,可以得出以下結論:
1、
Truncate比Delete所用的事務日志空間更少:
DELETE 是一行一行操作,并且把記錄都存進日志檔案(說明一下,無論任何恢複模式,都會記錄日志)。而TRUNCATE操作,是對一個頁操作,在日志中,僅僅記錄釋放頁面的這個動作,而不記錄每一行。
2、
Truncate比Delete使用鎖通常較少:
DELETE由于是一行一行删除,是以需要對處理的行進行加鎖,而且是行鎖。TRUNCATE操作由于是對頁操作,是以隻需要申請頁鎖或者表鎖。
3、
TRUNCATE對表中的所有頁都清空:
執行DELETE後,表還是會有空頁,但是TRUNCATE則會全部清除。但是TRUNCATE會保留表結構、列、限制、索引等。而DELETE之後,會哦他能夠過背景清除空頁。
為了更好地删除空間,可以使用以下方法:
(1)、在表中建立聚集索引
(2)、如果所有資料已經不要,那使用TRUNCATE
而不是DELETE,删除後DROP TABLE
。
另外,對于由于DELETE操作而留下的空間,會在插入時重用。如果覺得這些空間存在不好,那麼可以重建/建立聚集索引來釋放空間。