I came across the following text in a website:
---
The difference between DELETE TABLE & TRUNCATE TABLE is DELETE TABLE is
a logged operation, so the deletion of each row gets logged in the
transaction log, which makes it slow. TRUNCATE TABLE also deletes all
the rows in a table, but it won't log the deletion of each row, instead
it logs the deallocation of the data pages of the table, which makes it
faster. Of course, TRUNCATE TABLE can be rolled back.
---
What does 'deallocation of data pages of the table' mean?
Thanks,
ArpanDeallocation of data pages - the space formerly used by the rows in the
truncated table is freed, and can now be used to store new data. It's not,
hiowever, released to the operating system. The latter can be achieved
through the shrinking of the database.
Allocate - put to use;
Deallocate - release from use.
ML|||When a table is truncated, the space formerly used by the rows existing
in that table is freed which can now be used to store new
data...OK...that's fine but does this mean that when a table is
deleted, the rows formerly used by the rows existing in that table
isn't freed up which is why DELETE TABLE is slower than TRUNCATE TABLE?
Thanks,
Regards,
Arpan|||> data...OK...that's fine but does this mean that when a table is
> deleted, the rows formerly used by the rows existing in that table
> isn't freed up which is why DELETE TABLE is slower than TRUNCATE TABLE?
No, this is not why DELETE is faster than TRUNCATE. TRUNCATE is minimally
logged, I believe only the pages that are removed are logged, whereas with
DELETE, the entire process is logged. AFAIK, it has nothing to do with
being "freed up"...
No comments:
Post a Comment