SQL Server大表如何快速刪除數據

在SQL Server中，如何快速刪除大表中的數據呢？  回答這個問題前，我們必須弄清楚上下文環境和以及結合實際、具體的需求，不同場景有不同的應對方法。

1： 整張表的數據全部刪除

如果是整張表的數據全部清空、刪除，這種場景倒是非常簡單，TRUNCATE TABLE肯定是最快的。 反而用DELETE處理的話，就是一個糟糕的策略。

2： 大表中刪除一部分數據

對於場景1、非常簡單，但是很多實際業務場景，並不能使用TRUNCATE這種方法，實際情況可能只是刪除表中的一部分數據或者進行數據歸檔後的刪除。假設我們遇到的表為TEST，需要刪除TEST表中的部分數據。那麼首先我們需要對錶的數據量和被刪除的數據量做一個彙總統計，具體，我們應該採用下麵方法：

·           檢查表的數據量，以及要刪除的數據量。然後計算刪除的比例，

    sp_spaceused 'dbo.TEST';

    SELECT COUNT(*) AS DELETE_RCD WHERE TEST WHERE ......<刪除條件>

2.1 刪除大表中絕大部分的數據，但是這個絕大部分怎麼定義不好量化，所以我們這裡就量化為60%。如果刪除的數據比例超過60%，就採用下麵方法：

1： 新建表TEST_TMP

2:  將要保留的數據轉移到TEST_TMP

3： 將原表TEST重命名為TEST_OLD， 而將TEST_TMP重命名為TEST

4： 檢查相關的觸發器、約束，進行觸發器或約束的重命名

5： 核對操作是否正確後，原表（TEST_OLD）要麼TRUANCATE後，再DROP掉。要麼保留一段時間，保險起見。

註：至於這個比例60%是怎麼來的。這個完全是個經驗值，有簡單的測試，但是沒有很精確和科學的概率統計驗證。

另外，還要考慮業務情況，如果一直有應用程式訪問這個表，其實這種方式也是比較麻煩的，因為涉及數據的一致性，業務中斷等等很多情況。但是，如果程式較少訪問，或者在某個時間段沒有訪問，那麼完全可以採用這種方法。

2.2 刪除大表中部分數據，如果比例不超過60%

1：先刪除或禁用無關索引（無關索引，這裡指執行計劃不用到的索引，這裡是指對當前DELETE語句無用的索引）。因為DELETE操作屬於DML操作，而且大表的索引一般也非常大，大量DELETE將會對索引進行維護操作，產生大量額外的IO操作。

2：用小批量,分批次刪除（批量刪除比一次性刪除性能要快很多）。不要一次性刪除大量數據。一次性刪除大量記錄。會導致鎖的粒度範圍很大，並且鎖定的時間非常長，而且還可能產生阻塞，嚴重影響業務等等。而且資料庫的事務日誌變得非常大。執行的時間變得超長，性能非常糟糕。

批量刪除時，到底一次性刪除多少數量的記錄數，SQL效率最高呢？  這個真沒有什麼規則計算，個人測試對比過，一次刪除10000或100000，沒有發現什麼特別規律。（有些你發現的“規律”，換個案例，發現不一樣的結果，這個跟環境有關，有時候可能是一個經驗值）。不過一般用10000，在實際操作過程，個人建議可以通過做幾次實驗對比後，選擇一個合適的值即可。

案例1：

DECLARE @delete_rows INT;

DECLARE @delete_sum_rows INT =0;

DECLARE @row_count INT=100000

WHILE 1 = 1

    BEGIN

        DELETE TOP ( @row_count )

        FROM    dbo.[EmployeeDayData]

        WHERE    WorkDate < CONVERT(DATETIME, '2012-01-01 00:00:00',120);

        SELECT  @delete_rows = @@ROWCOUNT;

            SET @delete_sum_rows +=@delete_rows

            IF @delete_rows = 0

            BREAK;

        END;

SELECT @delete_sum_rows;

案例2：

DECLARE @r INT;

DECLARE @Delete_ROWS  BIGINT;

SET @r = 1;

SET @Delete_ROWS =0

WHILE @r > 0

BEGIN

    BEGIN TRANSACTION;

        DELETE TOP (10000) -- this will change

           YourSQLDba..YdYarnMatch

           WHERE Remark='今日未入' and Operation_Date<CONVERT(datetime, '2019-05-30',120);

          SET @r = @@ROWCOUNT;

          SET @Delete_ROWS += @r;

    COMMIT TRANSACTION;

    PRINT(@Delete_ROWS);

END

該表有下麵兩個索引

USE [YourSQLDba]

GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YdYarnMatch]') AND name = N'IX_YdYarnMatch_N2')

DROP INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch] WITH ( ONLINE = OFF )

GO

USE [YourSQLDba]

GO

CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch] 

(

    [Job_No] ASC,

    [GK_No] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  =