Delete and archive bulk record from SQL Server table with OUTPUT clause
One of my Facebook friend and old colleague who is Team Leader for one financial product asked me that he has a table with millions of records and wanted to archive few million records from that table, how he can do it?
Simple DELETE statement would make the huge transaction log file (if database is not simple recovery mode), lock the table for VERY LONG time and consume too much resource which will ultimately affect performance.
It is always good to keep short transaction and hence I always prefer to delete / update records in bunch, especially when there is a question of million rows in live production environment because sometime deleting millions of records in highly transaction environment take very long time, may be few hours or a day and if you cancel execution of delete query, everything will be roll backed which will again take a long time and if you are deleting in small bunch, like 1000 rows at a time and you stop execution, you will have to roll back maximum 1000 rows which won’t take long time.
Let me show you how can we do it by creating one sample database.
–Create sample database
CREATE DATABASE ExtremeAdvice
–create sample table along with 100,000 rows
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
DROP TABLE orders
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
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
IF OBJECT_ID(‘ordersArchive’, ‘U’) IS NOT NULL BEGIN
DROP TABLE ordersArchive
CREATE TABLE ordersArchive (OrderID INT, OrderDate DATETIME, Amount MONEY, Refno INT)
Now we have sample environment ready to use so let us create one stored procedure which will delete rows based on the condition we provide.
CREATE PROCEDURE [dbo].[SPordersArchive]
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
@dt = GETDATE(),
@rows = 0
–Deleting records in bunch
DELETE TOP (1000) o
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′
Let us now execute the stored procedure, it may take sometime and you will get message later on which will looks something like following screen capture.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.