Delete vs. Truncate

If I am deleting all the rows in a table why would I use Truncate Table over Delete, or vice vera? What is the difference?
KenThe Truncate is much faster.
The reason truncate does not record in the transaction log therefore you can not role it back

Eyal|||Okay,

That makes sense. I ran across it in a DTS package that someone else created, that basically adds new data to a table.

It creates temp tables, and one of them they were doing a delete on, which didn't quite make sense to me.

Thanks for the reply!

Ken|||You cannot Truncate from a table used in replication (because it is not logged).|||Originally posted by eschapir
The Truncate is much faster.
The reason truncate does not record in the transaction log therefore you can not role it back

I am not sure if you guys have experienced this. Truncate command may not work at times. And this is not just happening in my current job but previous job too. When truncate command was used, old records were not removed and when new set of data was loaded, it caused duplicates in the table.

So for peace of mind, I usually placed both truncate and delete commands (though delete will do the job) in some of my missing critical processes.

Sound really silly but it actually happened to me.

I am on SQL7 SP4.

Eyal|||TRUNCATE TABLE is aimed at administrative tasks.(hard come, light gone)

1. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

2. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

3. TRUNCATE TABLE may not be used on tables participating in an indexed view.

4. Cannot be used on linked server.

5. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

For more see BOL Topic "TRUNCATE TABLE" and other sources :)

Good luck !|||Thanks guys!

Now I know when to use each one. For this particular task I can use truncate. Actually I could just drop the table and recreate it. Delete take a lot longer to run for this particular process.

Doesn't really cause any issues either way, but when you don't have much time for jobs at night, speed and time saved is always a consideration!

Thanks again! Ken