如何在不失去數據的情況下刪除sql server中的大量數據？

在日常過程中，我一直在處理數百萬的數據刪除。基本上我有4張桌子。

Table_A
Table_B
Table_C
Table_D

我正在嘗試刪除所有表中超過 10 天的數據。

可能我會在每個表中刪除大約一百萬。我創建了一個儲存過程來執行這些操作。

我刪除數據的步驟是

第 1 步：將最近幾天（我必須保留的數據）移動到臨時表

select * into Table_A_Temp
from Table_A
where <<where clause last 10 days to till date>>

第 2 步：將主表重命名為舊表（包含所有日期數據的表）

exec sp_rename 'Table_A', 'Table_A_Old'

第 3 步：將臨時表重命名為主表（包含從最後幾天到現在的數據的表）

exec sp_rename 'Table_A_temp', 'Table_A'

步驟4：如果在復製過程中插入了任何新數據，則查詢帶有時間範圍的臨時表

Insert into Table_A
select * from Table_A_old

第 5 步：刪除舊表

DROP TABLE Table_A_old

步驟 6：在主表中創建鍵和約束（意味著重命名表）

code to create primary keys and constraints

問題： 如果我在儲存過程執行時連續向表中插入數據，我會失去幾秒鐘的數據。（所有 4 張桌子）

案例1：重命名表時

當我將主表重命名為舊表並將臨時表重命名為主表時

我收到無效對象錯誤（該表存在錯誤）

案例2：我的兩個表有外鍵關係如果我在創建約束和鍵之前插入數據，我會得到相關的錯誤。

如何在不失去數據的情況下正確處理和刪除數據。請建議最佳做法。

使用批量刪除。

DECLARE @keepgoing bit = 1;

WHILE (@keepgoing = 1)
BEGIN
 DELETE d
 FROM
 (
 SELECT TOP 100 *
 FROM Table_A
 WHERE Created < DATEADD(DAY, -10, GETDATE())
 AND NOT EXISTS (....FK check...)
 ORDER BY Created
) d;

IF (@@ROWCOUNT = 0) SET @keepgoing = 0;
END

只要您執行表重命名，除非您更改插入過程，否則您將無法在沒有停機的情況下完成任務。如果您可以調整插入過程以在失敗時執行重試，您可以克服這個缺點。

另一種選擇是省略表重命名並在一個表中執行所有操作。您可能已經意識到，在同一個表中刪除大量數據可能太慢，並解決了表切換策略。

我發現對於具有常量插入的單個表的最佳刪除策略是在儲存過程中（批量大小可以根據您的環境進行調整）：

DECLARE @MONTHCOUNT int
SET @MONTHCOUNT = 24 -- delete records older than 24 months

CREATE TABLE #deleteEntries (Id INT NOT NULL PRIMARY KEY);

INSERT INTO #deleteEntries ( Id )
SELECT deleteAlias.Id
FROM dbo.tableToDeleteFrom deleteAlias WITH (NOLOCK)
WHERE deleteAlias.SendDate < DATEADD(MONTH, -@MONTHCOUNT, GETDATE())

WHILE 1=1
BEGIN
   DELETE dbo.tableToDeleteFrom
   WHERE Id IN (
       SELECT TOP 10 d.Id
       FROM #deleteEntries d
       ORDER BY d.Id
   )

   DELETE #deleteEntries
   WHERE Id IN (
       SELECT TOP 10 d.Id
       FROM #deleteEntries d
       ORDER BY d.Id
   )

   IF @@rowcount<10
       BREAK
END

這種方法可以達到合理的數量，但在此之後您不能足夠快地刪除以跟上插入的速度。

為了設法刪除更多的數據，我建議進行表分區。這對我來說非常有效。在這裡，您將您的表分區，例如每日分區。像這樣，您已經為每天的數據進行了分組。一旦您想刪除特定日期的數據，因為它現在超過 10 天，您只需刪除此分區。刪除分區是通過將所需分區移動到備用表中然後截斷備用表來完成的。

此任務是即時操作，不會導致停機，無論您要刪除多少數據/行。

這裡有一些從表分區開始的連結：

• https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/
• https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/

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