Delete large amount of data from a table

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

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.

Reference:

SQL Server: Delete a Huge Amount of Data from a Table