Deleting Large Amounts of Rows from SQL 2005 (2008) Database Tables

Each time you perform an insert, update or delete operation on a database table it also affects the indexes for that particular table. If you need to bulk load or delete a mass of data from a highly indexed table, your best choice is to disable all the indexes on a table, perform the insert/delete operation, and then rebuild all the indexes.

Below I will demonstrate a quick way to delete a lot of data from a highly indexed database table in MS SQL 2005 or MS SQL 2008.  I used this method to delete 20 million rows from a table on a super active MS SQL database server.

-- 1)    Identify all existing indexes on a table

sp_helpindex [tableName]

-- 2)    Disable each nonclustered index in the list

ALTER INDEX [indexName1] ON [dbo].[tableName] DISABLE
GO
ALTER INDEX [indexName2] ON [dbo].[tableName] DISABLE
GO

-- Important: Do not disable the clustered index because it will prevent
-- access to table data until you drop or rebuild it.

-- 3)    Use new SQL 2005 (2008) DELETE TOP syntax to delete dynamically
--        declared number of rows in batches in order
--        to prevent extensive table locking:

Declare @BatchSize int
Set @BatchSize = 100000 -- select this number based on your context

Declare @RowsAffected int
Set @RowsAffected = 1

While (@RowsAffected > 0)
begin
    delete top (@BatchSize) from [tableName] where [your condition]
    set @RowsAffected = @@rowcount
end

-- 4) After the above operation completes its execution, rebuild
--     all the indexes you disabled in step 2:

ALTER INDEX [indexName1] ON [dbo].[tableName] REBUILD
GO
ALTER INDEX [indexName2] ON [dbo].[tableName] REBUILD
GO

-- Etc.