有效刪除表中 90% 的數據

我正在研究刪除 90% 的表數據的過程，因為測試只需要 10%。

我發現的最佳方法包括將 10% 的表行儲存到臨時表中。

目前方法

SELECT TOP 10 PERCENT *
INTO #temp_some_table
FROM some_table (nolock)
ORDER BY some_column DESC

TRUNCATE TABLE some_table 

INSERT INTO some_table 
SELECT *
FROM #temp_some_table 

DROP TABLE #temp_some_table 

此方法正在填滿 tempdb 並導致磁碟也填滿。

問題

有沒有更有效的方法來刪除表中 90% 的數據 ex ( DELETE TOP 90 PERCENT FROM sometable)

要麼

有沒有辦法使用批處理將 some_table 的 10% 的數據插入到臨時表中？像這樣的東西：

DECLARE @r INT;

WHILE @r > 0
BEGIN

BEGIN TRANSACTION;

INSERT INTO [dbo].[##temp_cds_Basket]
SELECT TOP 10 PERCENT *
FROM [dbo].[cds_basket] s

SET @r = @@ROWCOUNT;
print @r 

COMMIT TRANSACTION

END

可能的解決方案

這個怎麼樣？

SET NOCOUNT ON;

DECLARE @r INT;
DECLARE @TenPercentDate datetime 
with cte (some_column) as (

   select top 10 percent some_column from some_table (nolock) order by some_column desc
)
select @TenPercentDate = min(some_column)
from cte

select @TenPercentDate

SET @r = 1;

WHILE @r > 0
BEGIN
 BEGIN TRANSACTION;

DELETE TOP (10000) from
 some_table 
 WHERE some_column < @TenPercentDate  

 SET @r = @@ROWCOUNT;
 print @r

 COMMIT TRANSACTION;

 --CHECKPOINT;    -- if simple

END

--rollback

我在上面的評論中提到了三個不同的想法。這是對其中至少一個的一些詳細說明（由於自我診斷的隧道視覺，您會被困在上面）。

1. 好吧，您可以預先計算構成 10% 的行數，然後在您的批次中進行比較。

我正在考慮這個問題，但我們真的不需要進行比較 - 我們可以找出我們想要保留的邊緣的日期時間值，並刪除舊行的塊，直到沒有剩下的行。例如：

SET NOCOUNT ON;

DECLARE 
 @rc INT = 1, 
 @cutoff DATETIME, 
 @batchsize INT = 10000;

;WITH x(dt) AS 
(
SELECT TOP (10) PERCENT datetime_column
 FROM dbo.mytable 
 ORDER BY datetime_column DESC
)
SELECT TOP (1) @cutoff = dt -- earliest row we want to keep
FROM x
ORDER BY dt;

WHILE @rc > 0
BEGIN
 DELETE TOP (@batchsize) dbo.mytable
   WHERE datetime_column < @cutoff
 SET @rc = @@ROWCOUNT;
END

您可以查看這篇文章，了解一些增強此功能的其他方法。

2. 您可以考慮 drop / select into 而不是 truncate / insert，並且還可以考慮在此操作期間使用批量日誌恢復。

我認為這將是一個有效的選擇，並且應該比上述造成的痛苦更少。

3. 您也可以只執行一項數據移動操作：

SELECT TOP 10 PERCENT cols
 INTO dbo.newtable
 FROM dbo.oldtable
 ORDER BY datetime_column;

DROP TABLE dbo.oldtable;

EXEC sys.sp_rename N'dbo.newtable', N'dbo.oldtable', N'OBJECT';

這也沒有那麼痛苦，但可以隨意在其中註入一些調試，以確保在執行 drop 之前獲得所需的數據。

請注意，在後兩種情況下，可能存在約束、模式綁定視圖等阻止您刪除表（顯然，入站外鍵不是問題，因為您可以截斷，但出站可能仍需要待處理）。現有計劃也會產生影響（刪除 90% 的數據也會導致統計數據更新和計劃無效，所以真的沒有什麼不同），正如@Kenneth 在下面指出的那樣，您需要重新建立對新表（因此您可能希望確保可以提前編寫腳本）。

引用自：https://dba.stackexchange.com/questions/95085