使用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

让我们现在执行这个存储过程，它会需要一些时间，你会在屏幕上看到如下信息。