Truncate vs delete from

Question

Post reply

Truncate vs delete from

dba_user

Default port

Points: 1458
More actions
September 9, 2003 at 12:49 pm

#160762

Can someone refresh me the differences between using 'truncate' and 'delete from' especially on dealing with identity columns?
Thanks for your help, this forum has been extremely helpfu!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Truncate table basically is faster because of how it "removes" the data. There are limitations:

1) Must be db_owner, db_ddladmin, or table owner.

2) You can't use it on a table referenced by a foreign key constraint.

Truncate table will reseed the identity value, delete will not.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Ok, just let me rephrase one thing Brian states.

quote:
Truncate table basically is faster because of how it "removes" the data.

No data is actually removed the pages are marked free and the pointer to the indexes and first page are removed. The data still physically exists until overwritten or a shrink to truncate the free space is done. This is very minimal work and is logged for those actions.

Delete logs each rows data and physically removes the record from the pages.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Antares,

quote:
No data is actually removed the pages are marked free and the pointer to the indexes and first page are removed. The data still physically exists until overwritten or a shrink to truncate the free space is done. This is very minimal work and is logged for those actions.

some time ago I read somewhere, that TRUNCATE operations are minimally logged. Now I know what is logged and why.

Thanks!

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kosie Kwak Old Hand Points: 383 More actions · Answer 4

What impact does truncate have on logshipping? Will the truncate be 'replicated' to the standby database?

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 5

The truncate statement removes data by deallocating the data pages and logs the deallocation of data pages whereas the delete statement removes data one row at a time and logs the deletion of each row of data.

Cheers!

Arvind

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

In addition to that all the identity columns will be reset.

If you have the rights to perform a truncate table as Brian wrote you should prefer using it instead of delete without where clause if you do not mind not to be able te recover afterwords.

Bye

Gabor

Bye
Gabor

troytabor SSC Enthusiast Points: 191 More actions · Answer 7

Since TRUNCATE resets the identity column (either with a reseed, or setting an autoincrement back to 0) it should only be used if there is currently no data in other tables linking to the data in this table, or if you insert the identity field when you re-populate the table.

bnordberg SSCertifiable Points: 5480 More actions · Answer 8

I have a related question. I need to do a big delete of probably 40,000,000 rows from a 46,995,209 row table. I can't use truncate as I need the 6,995,209 rows, but I really don't want to log the delete. Is there a way to perform the delete without logging it?

KerryH SSChasing Mays Points: 632 More actions · Answer 9

One way to remove a large number of rows from a table is to do a SELECT INTO of the rows you wish to keep into a new table and then rename the tables, reapply permissions, etc.

Another way is to BCP out the rows you wish to keep by BCPing out using a view, truncate the table, and then BCP the rows back in.