Dropping an entire table

Question

I plan to delete an entire table with over 930,000 rows of data. Which is the best way to do it without increasing the Log size or increasing the DB size.

I am on a live site and my hosting has given me 150 MB of space..I am already using 125 MB and hence need to be careful of the DB size since increase in log will increase the size of my DB

Answer 1

use truncate table.

Answer 2

If you do not wish or need to fully log the deletion activity(i.e. you do not need to be able to recover your database to a specific point in time) then you can flush/deallocate the contents of the table by using the TRUNCATE TABLE command.

If on the other hand you wish to log the event fully then you should delete the data in batches in order to maximise performance, see the following article for details on how to do this:

Performing Fast SQL Server Delete Operations