Deleting large amounts of rows from SQL 2005 (2008) database tables07/27/2010
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.