>You can do this...
>
>TRUNCATE TABLE MyTable
>
>Basically, SQL Server will take the space occupied by the table and give it back to the operating system. It's like shooting a table dead in one killshot. Even if you have a trillion rows in the table, it will run almost instantly. SQL Server does not log the row deletions in this case, because there are no row deletions. (From the Fox world, I guess you could say it's not unlike the ZAP). SQL Server "does" log the de-allocation of space (and believe it or not, you can ROLLBACK from a TRUNCATE and it will work), but that's a very quick process.
>
>However, you cannot issue a TRUNCATE on a table that's involved in a constraint. You'd have to drop the constraints
>
>So again....and this is an over-simplification, but...
>
>SELECT * INTO TableWithRowsToRetain FROM MainTable WHERE (Condition for rows you want to keep)
>TRUNCATE TABLE MainTable
>
>INSERT INTO MainTable SELECT * FROM TableWithRowsToRetain
>
>Again, you might want to consider this if the # of rows being retained is smaller than the # of rows being deleted.
>
>Alternatively, instead of that last INSERT statement, you could physically DROP the MainTable and Rename the temp table back to the Main Table. Depending on how you want to rebuild indexes might determine which option you take.
>
>Also....Hugo's had good advice about deleting in chunks is also something I've read that people like to do.
>
>You've got several options here, all of which are probably better than trying to delete millions of rows with a DELETE statement.

Thanks for the additional information