We help IT Professionals succeed at work.

delete all versus drop table

bozer
bozer asked
on
Hello experts,

I have a recurring job that extracts some data from a table and inserts it into another table.

What I am currently doing is deleting all data from the target table and then do the extract from the originating table. (Because I always need the newest data)

I am wondering if that is a good practice in terms of performance. Or should I use Drop Table - Create Table each time?

Any comment or recommendation will be appreciated.

Thanks in advance
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> delete all versus drop table

If you have Recovery model set to Full, then both Delete and Drop table records all changes in Transactional logs and hence will take almost the same time..
If that was a temporary/ intermediate table without any Foreign keys defined on the table, then you can Truncate the table which would not log changes and hence it would perform better..

Hope this answers

Commented:
it's all depends on the size of the table and recovery settings for  Your table. For big tables ,more then 1 mil rows drop may be faster then delete all .Think of it as of a file,what's faster ,delete from disk(drop) or open in notepad and delete everything?

Author

Commented:
Thanks