Truncate Table vs Drop Table

[DBAWinnipeg]

Can anyone tell me how Truncate Table and Drop Table are different? I know (obviously) DROP TABLE actually gets rid of the table but in doing the DROP TABLE does SQL just truncate the data and then drop the object?

The reason I ask is that if I truncate a 400 million row table it takes approx 3 minutes but if I drop that same table it takes approx 5 minutes. Not a big deal I know but just curious as to why the time difference



Thanks in advance!!!

Colin in da 'Peg

 

[jbenson001]

I belive Drop table deletes from the table first, which is a logged operation. Truncate table does not log.

 

[mrdenny]

When you drop the table it also checks constraints, FKs, etc. It also has to remove any indexes, and the Primary key before it can drop the table and remove it from the system files.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[bborissov]

When you DROP table ALL trigers, Constrains etc. related to this table must be droped also. So SQL Check for validity and the deside can it drop the table or not. TRUNCATE just removes the records (I hope I am right )

Borislav Borissov

 

[DBAWinnipeg]

Thanks