How to do a fast DELETE of lots of data from a large table (sql server)

Question

I want to delete about 90% of rows from a million+ row table. Anything I can do to make this faster? e.g. if I turn on Simple Recovery Mode will this help?

Answer 1

Copy the rows your don't want to delete into a temporary table using select into, and then truncate the table rather than delete. The copy the rows back into the old table. Remember to drop contraints first.

If you have identity columns, after renter the data, use something like this to reseed the table.

declare @seed int
select @seed = max(identity_col)+1 from tableName
DBCC CHECKIDENT (orders, table_name, @seed)

Answer 2

Given that you want to delete 90% of rows, it might be fastest to SELECT INTO those 10% rows you want to keep into a new table.