Fast way to Remove the rows

Hi There

We have a table with almost millions of rows . Now as per business requirement we need to remove all the rows and left with only 100,000 rows. When i use regular delete command it takes like forever.
Is there any other way to solve this.

Thanks advance.
Danny

Create a new table, insert into it only the rows you want to keep.
truncate and drop the old table. Rename the new table to be your old one.

Remembering to recreate all the required Primary Keys, Indexes, Foreign Key Constraints, etc etc etc

Thanks alot for solution .This worked a lil faster as anyway there are 100,000 rows.
But just for curousity sake is not there any other way then creating new table and all stuff. Any delete or system funcion tht do it quickly.

Thanks alot for support
Danny

is there an index on the fields in your WHERE clause ?

Eg, if youre saying DELETE FROM MYTABLE WHERE MyDateField <= '2006/01/01', and the datefield has no index on it, the delete will be slower than it could be

Other than Thomos solution is Chappy's but it takes long time as well as it will block all the users accessing this table... because sql will get the table level locks when you are delete 100 mill. rows.

If you are not in hurry you can use delete statement but use while loop do delete 1000/5000 at a time.

while 1 =1
begin
set rowcount 1000
DELETE FROM MYTABLE WHERE MyDateField <= '2006/01/01'
if @@rowcount = 0 break
end


Mohammed U.

Chappy - The field we are using in Where clause have the Index but still Million rows is the main issue.

MuhammedU - Using while loop is fine but can you please explain how it is useful if we breakdown the number of rows in smaller ones.

Thanks All
Danny

Recently I did some DELETE performance research,

first query processor tried to find out where your targeted data rows are, per the SQL profiler results, this causes huge disk-reads if no appropriate index can be used for large table.

secondly, sql server takes action to 'delete' your data rows, for heap and table with clustered index, the DELETE action is different.


------------------
Bug explorer/finder/seeker/locator
------------------

If you use while loop...1000 row at a time...sql gets exclusive locks on 1000 rows only not the entire table...so that you can avoid blocking...

I use both methods delete the data from production based on the data and table usage.



Mohammed U.

Thanks everybody for all your suggestions.

Best
Danny