SQL Tips by Namwar Rizvi

June 28, 2007

Quick delete technique for huge tables

Deleting a huge table like purging old transaction records of millions of rows takes quite a lot of time and since DELETE is a fully logged operation therefore, if something goes wrong during deletion the whole process will be rolled back which itself is a time consuming process.
SQL Server 2005 enhanced the functionality of TOP operator and it can be used with Data modification statements like INSERT,UPDATE and DELETE.

By using the TOP operator, you can divide your time consuming delete operation into the batches of small deletions like 5000 records per deletion. The benefit of this approach is:

– Log space will be re-used again and again which will keep your log file size in control.
– If operation fails at any time then only last batch will be rolled back and you can start purging again quickly after recovering from the failure.

Following is the TSQL code to perform batch deletions:

Use AdventureWorks
Go

–Create a copy of Sales.SalesOrderHeader as a test table
Select *
into testOrderHeaderTable
from Sales.SalesOrderHeader

–Delete 5000 Rows at a time
While 1=1
Begin
Delete Top(5000) testOrderHeaderTable

—Check for exit condition
If @@rowcount <5000 break
End

–Remove the test table
Drop table testOrderHeaderTable

Advertisements

4 Comments »

  1. Support in IIS, no servers show up for me to select from. Do I not have something installed or configured?

    I have SQL Server 2000, SP4 Developer Edition, Version 8.00.2039 as the default instance and SQL Server 2000, SP4 Desktop Engine, Version 8.00.2039 as another instance.

    Comment by Arleen — August 7, 2007 @ 3:33 AM | Reply

  2. I think if we copy a table into another table if the size is big, then it may take a huge amount of space. any comments about this?

    Comment by soundararajan — March 21, 2008 @ 3:06 PM | Reply

  3. delete top can i use where clause ??

    Comment by werty80 — May 14, 2008 @ 7:08 PM | Reply

  4. I tried using a WHERE clause… This is a table with 6 million records, I used these 2 queries to test:

    1) DELETE FROM myTable where dateField > ‘20100318’

    2)
    WHILE(1=1)
    BEGIN
    DELETE TOP(5000) FROM myTable WHERE dateField > ‘20100318’
    IF @@ROWCOUNT < 5000 BREAK
    END

    The first one took exactly 8'20'' to delete 3,164,330 records. The second one took exactly 8'40'' to delete the same ammount records, so, the "quick delete technique for huge tables" took longer to complete.

    I haven't tried without a WITH clause, though.

    Comment by Darth Reisender — March 18, 2010 @ 10:32 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: