Delete large amount of data from a table 刪除大量資料作法

Delete large amount of data from a table
刪除大量資料作法
from my MSDN blog - August 30, 2016

Method 1

若刪除完成之後留下的資料較多的話(例如要刪除1/3的資料)，就用WHILE DELETE top語法來刪除

declare @n int
while 1=1
begin
DELETE top(2000)
FROM dbo.BigTable
WHERE time <= '2013-09-03 22:00:00.000'
OPTION(MAXDOP 1) -- 可考慮是否只使用一個CPU來執行刪除動作
set @n=@@ROWCOUNT
if @n<2000
break
end

Method 2

若留下的資料比較少(例如要刪除2/3的資料或更多的資料)，就可以考慮INSERT INTO再TRUNCATE或INSERT INTO再RENAME

INSERT INTO and TRUNCATE
1.將要保留的資料INSERT INTO到dbo.Temp_BigTable

SELECT * INTO dbo.Temp_BigTable
 FROM dbo.Temp_BigTable
 WHERE Date < '2015/1/1';

2.清空dbo.Temp_BigTable

TRUNCATE TABLE dbo.Temp_BigTable;

3.INSERT INTO dbo.BigTable from dbo.Temp_BigTable

INSERT INTO dbo.BigTable
SELECT * FROM dbo.Temp_BigTable;

可以參考這篇 SQL Server: Delete a Huge Amount of Data from a Table

INSERT INTO再RENAME
1.將要保留的資料INSERT INTO到dbo.Temp_BigTable
2.DROP TABLE dbo.Temp_BigTable
3.RENAME dbo.Temp_BigTable to dbo.BigTable

注意:
因為原Table會被刪除，所以需事先調查與保存與重新設定以下項目
1.權限
2.Trigger
3.Index

PS.以下狀況無法直接DROP TABLE
1.被Foreign Key或view with SCHEMABINDING reference的資料表
2.複寫發行資料表
3.啟用CDC的資料表

若有view with schemabinding

CREATE VIEW v_Table_2
WITH SCHEMABINDING

DROP TABLE會出現以下錯誤

Msg 3729, Level 16, State 1, Line 2
Cannot DROP TABLE 'dbo.Table_1' because it is being referenced by object 'v_Table_2'.

若有Foreign key reference

DROP TABLE會出現以下錯誤

Msg 3726, Level 16, State 1, Line 2
Could not drop object 'dbo.Table_1' because it is referenced by a FOREIGN KEY constraint.