RE: Removing rows from very large table with indices

From: Umut Nazlica (anonymous_at_discussions.microsoft.com)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 00:51:06 -0800

Hi,

I don't recommend you to delete indexes because proper indexes will help you to locate the records that you want to delete more quickly . (You have to find the records first before you delete them.)

If you want to delete records in a single transaction (and if you are deleting large amount of data) your log file will grow fast no matter what you're recovery model is.

I'll prefer to set rowcount and delete records in a while loop until @@rowcount = 0. This will delete you're records in smaller transactions.

Also selecting the records that you like to keep in a new table and dropping the old table and renaming the new table is generally less expensive then delete. (Depends the percentage of tha data that you want to keep and you want to delete.)

And partitioned tables are very helpfull for archiving and deleting..

Regards..

Umut Nazlıca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)