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: