Delete and archive bulk record from SQL Server table with OUTPUT clause

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.

[sourcecode language=”sql”]
–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
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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.

1DeleteMessage

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Audit Trail with OUTPUT clause in SQL Server 2005/2008/Denali

Audit Trail is one of the task client asked for in most of the project so that they can keep watch on crucial fields regarding its change.
I have seen many small & few medium sized companies is still not hiring SQL Server professional and used to go ahead with .NET developer. .NET developer generally keep their knowledge updated with .NET technology but they lack of knowledge in SQL Server domain so for Audit Trail, they still rely on old pseudo table (INSERTED, DELETED) accessible in Trigger. But in SQL Server 2005 and later, you don’t even need to rely on Triggers for Audit Trail but you can use new “OUTPUT” clause.
“OUTPUT” clause is still much underutilized feature even after 7 years. That is the reason I am writing on Audit trail and OUTPUT clause once again.
Within “OUTPUT” clause, you can access pseudo table and generate your “Audit Trail” log. I will show you with an example.
We will have one table of Test comes to an environmental company to analyze, if client change the Test s/he wants to perform, we have to keep trail on that, if he cancel any test, we have to keep trail on that too.
–we will keep “Audit Trail” for below given table
create table ClientOrder
(
      OrderID varchar(5)
      ,ClientID varchar(5)
      ,Test varchar(20)
      ,OrderDate datetime default getdate()
)
GO
–following is the table, we will keep “Audit Trail” in.
–this will keep track of all data changed
CREATE TABLE AuditOfOrder
(
      Id INT Identity(1,1)
      ,OrderID varchar(50)
      ,OldTest varchar(20)
      ,NewTest varchar(20)
      ,DMLPerformed varchar(15)
      ,ChangeDate datetime default getdate()
)
GO
–inserting data in “ClientOrder” table
–all insert will be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘VOCMS Group1’)
GO
–inserting data in “ClientOrder” table
–all insert will be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘Pesticide Group1’)
GO
–let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–we will perform UPDATE on “ClientOrder” table
–which will be recorded in “AuditOfOrder” table too
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
UPDATEClientOrder
      SET Test =‘SVOC Stars’
OUTPUT
      inserted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      inserted.Test AS NewTest,
      ‘UPDATE’ as DMLPerformed
WHEREClientOrder.Test=‘VOCMS Group1’
) t
GO
–let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–Finally the log of Delete will be stored in the same way.
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
DELETE FROM ClientOrder
OUTPUT
      deleted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      deleted.Test AS NewTest,
      ‘DELETE’ as DMLPerformed
WHEREClientOrder.Test=‘SVOC Stars’
) t
GO
–let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
Isn’t this interesting? Start using it.
BTW, I have previously written some articles regarding Audit Trail techniques (old & new both), if you want to refer it, have a look at below links:
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT Clause in SQL Server 2008/2005

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.
You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.
Let us see its usefulness by one small example.
–create table for demo and insert few records
create table testOutPut(tid int identity(1,1), name varchar(50))
insert into testOutPut (name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Pinal’ union all
select ‘Bihag’ union all
select ‘John’ union all
select ‘Bhaumik’ union all
select ‘Avi’ union all
select ‘James’
go
–check whether all records came with proper tid
select * from testOutPut
go
–create temp duplicate table which will store data which are deleted
create table #deleted (id int, name varchar(50))
–deleting records from testOutPut table and inserting those deleted
–records into temp table with help of OUTPUT clause and DELETED pseudo table
delete testOutPut
output DELETED.* into #deleted
where tid < 3
–checking temp table
select * from #deleted
–this will show deleted data on screen
–but won’t store anywhere like we did in previous snippet
–and stored data in #deleted (temp table)
delete testOutPut
output DELETED.*
where tid >3
select * from testOutPut
go
select * from #deleted
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

BSA (Body Surface Area) calculation in stored procedure with OUTPUT parameter in Microsoft SQL Server 2005:

Herewith, I am giving you one more example of OUTPUT parameter in Stored Procedure for calculating BSA (Body Surface Area). You are in development of medical software than BSA is not a new term for you. If your body surface are is between 1 to 2 than it is normal as per my little knowledge about BSA. I am giving a sample example which will calculate BSA based on the given height and weight. Height and Weight should be either in kg (weight) and cm (height) or in lbs (weight) and inch (height). You can make it more customize by giving more dynamic conversions.

–CREATING stored procedure to return BSA (Body Surface Area)

–The calculation is from the formula of DuBois and DuBois:

–BSA = (W 0.425 x H 0.725) x 0.007184

–where the weight is in kilograms and the height is in centimeters.

–DuBois D, DuBois EF. A formula to estimate the approximate surface area

–if height and weight be known. Arch Intern Medicine. 1916; 17:863-71.

–Wang Y, Moss J, Thisted R. Predictors of body surface area.

CREATE PROC CalcBSA

@option INT,

@weight FLOAT,

@height FLOAT,

@bsa FLOAT OUTPUT

AS

SET NOCOUNT ON

–if weight and height are in kg and cm accordingly

IF @option=1

BEGIN

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

–if weight and height are in lbs and inch accordigly

ELSE

BEGIN

SET @weight=(@weight/2.2046)

SET @height=@height*2.54

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

GO

–once you done with creating stored procedure, let us see whether actually it works!!!!

DECLARE @BSA FLOAT

EXECUTE calcbsa 1,84,180,@BSA OUTPUT

PRINT @BSA

GO

If you are new to stored procedure and wants to study it than do have a look at my following basic articles. Those articles contain from basic definition of stored procedure to different usage of SP.

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

http://www.sqlhub.com/2009/03/return-data-with-output-parameter-from.html

http://www.sqlhub.com/2009/03/dml-insert-with-multiple-ways-in-sql.html

http://www.sqlhub.com/2009/03/delete-many-multiple-records-in-bunch.html

http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

http://www.sqlhub.com/2009/03/create-your-own-system-stored-procedure.html

Reference: Ritesh Shah
http://www.SQLHub.Com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Return data with OUTPUT parameter from Stored Procedure in SQL Server 2005

Herewith, I am keeping my promise and moving ahead with topic of stored procedure. I will be explaining how to return data from stored procedure in SQL Server with OUTPUT parameter. If you are new to Stored Procedure than I kindly advice you to move to my prior article about stored procedure at:

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

When you have need to return data to the calling procedure, you should use of OUTPUT parameter of SQL Server in Stored Procedure.

If you are returning record set for single value, I strongly insist to use OUTPUT parameter as it is much much faster than returning the value.

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating stored procedure which will return data with OUTPUT parameter

CREATE PROC getEmpDeptbyEmpName(@EmpName VARCHAR(50),@EmpDept VARCHAR(10) OUTPUT)

AS

SELECT @EmpDept=dept FROM emps WHERE Name=@EmpName

GO

–calling SP and catching return value in @EmpDept

DECLARE @EmpDept VARCHAR(50)

EXECUTE getEmpDeptbyEmpName ‘Ritesh’, @EmpDept OUTPUT

SELECT @EmpDept AS ‘Department’

GO

Reference: Ritesh Shah
http://WWW.SQLHub.Com
Note: Microsoft Book online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT (Deleted and Inserted) from insert, delete and update statement in SQL-Server 2005 (kind of pseudo table of Trigger):

You may remember pseudo table in trigger from which we can get manipulated data. There was no way out to access that pseudo table outside the trigger before Microsoft SQL Server 2005. The same concept you can get outside of trigger as well in Microsoft SQL Server 2005.

Let us create one table for demonstration

–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

–insert records

Insert into Car (CarName,CarDesc) values (‘Honda Pilot’,‘SUV’)

Insert into Car values (‘Honda CRV’,‘SUV’)

Return data from INSERT statement with INSERTED table

Insert into Car

OUTPUT INSERTED.* –this statement will return all the field of CAR table

— from INSERTED table

values (‘Honda CRV’,‘SUV’)

GO

Return data from DELETE statement WITH DELETED table

DELETE FROM CAR

OUTPUT DELETED.* –this statement will return all records

–which are just deleted based on where condition

where ID=1
GO

Return data from UPDATE statement from INSERTED and DELETED table.

UPDATE CAR SET CarDesc=‘Luxury car’

OUTPUT DELETED.CarDesc as ‘Old Value’, INSERTED.CarDesc as ‘New Value’

WHERE ID=2
GO

Enjoy the power of Microsoft SQL Server 2005

Happy SQLing!!!!!

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah