使用OUTPUT從句從SQL Server表刪除和歸檔大量記錄

使用OUTPUT從句從SQL Server表刪除和歸檔大量記錄

英文原文：

http://blog.extreme-advice.com/2013/01/08/delete-and-archive-bulk-record-from-sql-server-table-with-output-clause/

我Facebook上的一個朋友和老同事，他是一個財務產品的團隊領導，問我他有一個幾百萬記錄的表，想去從這個表歸檔一些記錄，他該如何做？

簡單的DELETE語句會產生大量的日志文件（如果數據庫不是簡單恢復模式），會鎖住該表非常長時間，并消耗大量資源而極其影響性能。

通常保持簡短的事務是非常好的，我總是喜歡批量DELETE/UPDATE記錄，尤其是當有大量的記錄在生產環境中，因為在達賴那個事務環境刪除大量記錄需要很長時間，如果你取消DELETE操作可能需要數小時或一天時間，一切將會被回滾，也將會花費很長時間，如果你小批量刪除，像每次1000行，而你停止執行，你將只回滾最大1000行，而不用多長時間。

讓我們通過創建一個示例數據庫來展示如何實現。

--Create sample database
CREATE DATABASE ExtremeAdvice
GO
USE ExtremeAdvice
GO
--create sample table along with 100,000 rows
IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN
DROP TABLE orders
END
GO
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
DATEADD(minute, ABS(a.OBJECT_ID % 50000 ), CAST('2012-02-01' AS DATETIME)),
ABS(a.OBJECT_ID % 10),
CAST(ABS(a.OBJECT_ID) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
IF OBJECT_ID('ordersArchive', 'U') IS NOT NULL BEGIN
DROP TABLE ordersArchive
END
GO
CREATE TABLE ordersArchive (OrderID INT, OrderDate DATETIME, Amount MONEY, Refno INT)
GO

現在我們已經準備好了示例環境，讓我們創建一個存儲過程，基于我們提供的條件刪除記錄。

USE [ExtremeAdvice]
GO
CREATE PROCEDURE [dbo].[SPordersArchive]
AS
SET NOCOUNT ON
DECLARE @cnt INT, @rows INT
SELECT @cnt = 1
DECLARE @msg VARCHAR(1024)
DECLARE @dt DATETIME
--creating infinite loop which will break itself whenever all record gets deleted based on condition given
WHILE 1=1
BEGIN
SELECT
@dt = GETDATE(),
@rows = 0
--Deleting records in bunch
DELETE TOP (1000) o
OUTPUT
deleted.OrderID,
deleted.OrderDate,
deleted.Amount,
deleted.RefNo
INTO ordersArchive(
OrderID
,OrderDate
,Amount
,RefNo
)
FROM Orders AS o (NOLOCK)
WHERE o.OrderID <=50000
SELECT @rows = @@ROWCOUNT
SELECT @cnt = @cnt + 1
SELECT @msg = 'Lap : ' + CAST(@cnt AS VARCHAR) + ' ARCHIVED ' + cast(@rows AS VARCHAR) + ' rows in ' + cast(DATEDIFF(second, @dt, GETDATE()) as varchar) + ' seconds'
RAISERROR(@msg, 0, 1) WITH NOWAIT
IF @rows = 0 BREAK;
WAITFOR DELAY '00:00:00.100'
END

讓我們現在執行這個存儲過程，它會需要一些時間，你會在屏幕上看到如下信息。