How long will a truncate table take on 252M Rows (170GB data)?

Question

I understand that there are many variables in this and am not looking for an exact number of min:sec :) I've read many resources that state that a TRUNCATE uses far less resources than a DELETE and hence will perform faster. But there are no order of magnitudes mentioned. As a TRUNCATE is a DDL (Data Definition Language) operation and not a DML (Data Manipulation Language) operation and only the system tables are part of the transaction, I'm presuming that it should not take very long.

Anyone with experience on doing this with a table anywhere near this size that can lend some insight? We talking up to a minute, up to an hour, days, weeks?

Answer 1

A millisecond or so.

Truncate is O(1) - a pure metadata operation.

This is assuming there is no concurrent activity on the table.

Truncate will potentially need to wait until existing reads are done to acquire a Sch-M (schema modification) lock.

For large tables, the actual deallocation is done asynchronously on a background thread (SQL Server 2000 SP3 onward).

For some other myths surrounding truncate, see A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged by Paul S. Randal.