FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to delete from a table with 800 million rows?

Large tables should have a Status char(1) columnm, isDeleted bit column or similar to indicate active or inactive status. Instead of deleting, the row status indicator can be updated. Purging of inactive records can be scheduled periodically.

Delete on large tables is slow especially if indexes involved.

Consider using SELECT INTO a new table what to be kept.

If DELETE cannot be avoided, it should performed in small batches like 10K rows. Example follows.

USE tempdb;

SELECT ProductID=CONVERT(int,ProductID), ProductName=Name,

       ProductNumber, ListPrice, Color

INTO Product

FROM AdventureWorks2008.Production.Product

GO

INSERT Product SELECT * FROM Product  -- Increase table rows for testing

GO 10

SELECT COUNT(*)FROM Product

GO

-- 516096

DECLARE @BatchSize int = 10000

-- Delete loop with 1 sec delay allowing other processes to use the table

WHILE (@@ROWCOUNT > 0)

BEGIN

  WAITFOR DELAY '00:00:01'

  DELETE TOP (@BatchSize) FROM Product

  WHERE ListPrice = 0

END

GO

SELECT COUNT(*)FROM Product

GO

-- 311296

DROP TABLE tempdb.dbo.Product


Exam Prep 70-461
Exam 70-461