使用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 databaseCREATE DATABASE ExtremeAdviceGOUSE ExtremeAdviceGO--create sample table along with 100,000 rowsIF OBJECT_ID('orders', 'U') IS NOT NULL BEGINDROP TABLE ordersENDGOCREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)GOINSERT INTO orders (OrderDate, Amount, Refno)SELECT TOP 100000DATEADD(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 aCROSS JOIN sys.all_objects bGOIF OBJECT_ID('ordersArchive', 'U') IS NOT NULL BEGINDROP TABLE ordersArchiveENDGOCREATE TABLE ordersArchive (OrderID INT, OrderDate DATETIME, Amount MONEY, Refno INT)GO

现在我们已经准备好了示例环境，让我们创建一个存储过程，基于我们提供的条件删除记录。

USE [ExtremeAdvice]GOCREATE PROCEDURE [dbo].[SPordersArchive]ASSET NOCOUNT ONDECLARE @cnt INT, @rows INTSELECT @cnt = 1DECLARE @msg VARCHAR(1024)DECLARE @dt DATETIME--creating infinite loop which will break itself whenever all record gets deleted based on condition givenWHILE 1=1BEGINSELECT@dt = GETDATE(),@rows = 0--Deleting records in bunchDELETE TOP (1000) oOUTPUTdeleted.OrderID,deleted.OrderDate,deleted.Amount,deleted.RefNoINTO ordersArchive(OrderID,OrderDate,Amount,RefNo)FROM Orders AS o (NOLOCK)WHERE o.OrderID <=50000SELECT @rows = @@ROWCOUNTSELECT @cnt = @cnt + 1SELECT @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 NOWAITIF @rows = 0 BREAK;WAITFOR DELAY '00:00:00.100'END

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